Excel TEXT Function Generator
Format a number or date as text using a format code, for labels and joined strings.
TEXT
Format a number or date as text using a format code, for labels and joined strings.
The number or date to format.
In quotes. Try "#,##0.00", "0.0%", "yyyy-mm-dd", or "$#,##0".
=TEXT(A2, "yyyy-mm-dd")Worked Example
Joining a date into a sentence, where plain concatenation would show the serial number.
="Invoice dated " & TEXT(A2, "d mmmm yyyy")Returns: Produces "Invoice dated 5 August 2026" instead of "Invoice dated 46238".
Checks Before You Paste
- •TEXT returns text, not a number. The result will not sum, and it sorts alphabetically - so format for display only, and keep the underlying value in its own cell.
- •Common codes: "#,##0.00" for two decimals with separators, "0.0%" for a percentage, "yyyy-mm-dd" for an ISO date, "mmmm" for a full month name, "$#,##0" for whole currency.
- •Format codes are locale-dependent. A workbook written with "yyyy-mm-dd" opens correctly elsewhere, but codes typed in a non-English Excel may use different letters for year and day.
How TEXT works
Availability: TEXT is in every version of Excel, in Excel for Mac and for the web, and in Google Sheets, which accepts nearly all the same format codes. Format codes are language-specific: an Excel installed in another language uses translated letters for the date and number placeholders, and translates them again when the file is opened elsewhere. Codes copied from an English-language website may need adjusting on a non-English installation.
TEXT applies a number format to a value and returns the result as text. It is the difference between a cell that displays 1,250.00 because of how it is formatted and a string that literally contains the characters 1,250.00. The first still adds up; the second does not. That distinction is the whole point of the function and also its main trap.
Reach for TEXT when the formatted value has to travel somewhere formatting cannot follow: into a concatenated sentence, a chart label, a file name, a merged report line. ="Invoice dated " & A2 produces Invoice dated 46082, because joining a value reads the number underneath rather than the format on top. ="Invoice dated " & TEXT(A2, "d mmmm yyyy") produces Invoice dated 1 March 2026.
The format codes are the same ones in Format Cells > Custom, so the fastest way to build a tricky one is to format a cell until it looks right, open that dialog, and copy the code out. 0 forces a digit and pads with zeros, # shows a digit only when there is one, the comma in "#,##0" turns on thousands separators, and text in quotes inside the code is printed literally. Colour and alignment codes such as [Red] and the asterisk fill character are accepted but ignored, because plain text has no colour.
Dates and times carry one ambiguity worth memorising: m means month in a date context and minute in a time context. Excel decides by what surrounds it - an m immediately after h or immediately before s is read as minutes, and everywhere else as months. "hh:mm" gives hours and minutes; "mm/dd" gives month and day. Square brackets around a time unit make it cumulative rather than wrapping, so "[h]:mm" shows 27:30 where "h:mm" would show 3:30.
Syntax
=TEXT(value, format_text)| Argument | Required | What it does |
|---|---|---|
value | Required | The number, date, time or formula result to format. Text passed in is returned unchanged in most cases, since there is no number to format. The cell's own display format is irrelevant - TEXT works from the stored value. |
format_text | Required | A number format code in double quotes, using the same syntax as Format Cells > Custom. 0 is a required digit, # an optional one, . the decimal point, , the thousands separator, % multiplies by 100 and appends the sign, and text inside quotes-within-the-code prints literally. Date and time placeholders are d, m, y, h, s and their repeats. Colour codes such as [Red] and fill characters are accepted but have no effect on text output. |
More worked examples
A2 holds the number 4521 - a five-digit reference whose leading zero was lost when the column was imported as numeric.
=TEXT(A2, "00000")Returns: 04521
Each 0 in the code forces a digit position, padding with zeros where the number is short. Using "#####" instead returns 4521, because # only shows a digit when one exists.
A2 holds the date 14 March 2026 and a chart needs a short month-and-year axis label rather than a full date.
=TEXT(A2, "mmm yyyy")Returns: Mar 2026
mmm gives the three-letter month, mmmm the full name, and mmmmm just the initial. The result is text, so a chart axis built on it sorts alphabetically - keep the underlying dates for sorting and use the text only as a label.
A2 holds the timestamp a job started and B2 the timestamp it finished the following day, and the timesheet needs total hours rather than a clock time.
=TEXT(B2-A2, "[h]:mm")Returns: 27:30
The square brackets tell Excel not to roll over at 24 hours. Without them, "h:mm" on the same difference shows 3:30, discarding the whole day - one of the quietest ways a timesheet can be wrong.
Common mistakes
- The result is text, so the column stops adding up
- =SUM(B2:B100) over a column of TEXT results returns 0, and sorting puts "10" before "9" because text sorts character by character. Keep the raw values in their own column and apply TEXT only where the formatted string is needed, or apply a cell number format instead - which changes the display without changing the value.
- m read as minutes instead of months
- =TEXT(A2, "h:m") on a date returns the hour and minute, not the hour and month, because an m following h means minutes. Conversely =TEXT(A2, "mm") on its own returns the month. When both appear, order them so the intent is unambiguous: "dd/mm/yyyy hh:mm" reads correctly, whereas "mm hh:mm" is asking for trouble.
- Percentage codes double-converting the value
- =TEXT(0.0725, "0.0%") gives 7.3%, which is right. =TEXT(7.25, "0.0%") gives 725.0%, because the % code multiplies by 100 - the value has already been converted to a percentage by hand. Decide once whether your source column holds 0.0725 or 7.25 and format accordingly.
- TEXT rounds the display while the source keeps full precision
- =TEXT(A2, "0.00") on 12.3456 shows 12.35, but the cell still holds 12.3456 and any total computed from the raw values will not match the sum of what is displayed. Where the number itself must change - invoice lines, allocations that have to reconcile - use ROUND on the value and let TEXT or the cell format handle only the display.
- Date codes behaving differently on a colleague's machine
- Format codes use the letters of the language Excel was installed in, and "dd/mm/yyyy" typed in an English installation is translated when the file opens in a German one. That normally works invisibly. It breaks when a code is pasted in from a website written for a different locale, or built by concatenating strings, in which case Excel returns #VALUE! or an unexpected layout. Rebuild the code through Format Cells > Custom on the machine that will use it.
Frequently Asked Questions
Because TEXT returns text by definition - that is what it is for. SUM ignores text, so a column of TEXT results totals to 0. If you only wanted the number to look different, do not use TEXT at all: select the cells and apply a number format from Home > Number, which changes the appearance while leaving a real number underneath. Use TEXT only when the formatted value has to become part of a string.
Pad with the 0 placeholder: =TEXT(A2, "00000") turns 4521 into 04521 and leaves an already-five-digit number alone. Each 0 in the code is a digit position that gets filled with a zero if the number is short. For a code that is text rather than a number, TEXT is unnecessary - the zeros are already there, and the problem is usually that the column was imported as numeric in the first place.
Cell formatting changes how a value is drawn and leaves the value untouched, so the cell still calculates, sorts and sums as a number. TEXT produces a new value that is a string of characters, which does none of those things. Format the cell when the number stays in its own cell; use TEXT when the formatted version has to be joined into a sentence, a label or a file name where formatting cannot travel with it.
Excel decides between month and minute from context: an m directly after an h, or directly before an s, is read as minutes; anywhere else it means months. "hh:mm" is hours and minutes, "mm/dd" is month and day. If you need minutes away from an hour code, write it as "[m]" in square brackets to force the cumulative minute interpretation.
Put the leading unit in square brackets: =TEXT(B2-A2, "[h]:mm") shows 27:30 rather than rolling over to 3:30. The same applies to "[m]" for total minutes and "[s]" for total seconds. Without the brackets Excel treats the value as a clock time and discards the whole days, which is why timesheet totals so often come out roughly a day short.
Related Tools
CONCATENATE Generator
Join the formatted string into a sentence without losing the formatting.
TEXTJOIN Generator
Combine several formatted values with a delimiter in one step.
Excel Date Converter
Convert between serial numbers and real dates before formatting them.
Convert Text to Number
Turn TEXT output back into a calculating number when it has to be summed.