TEXT function
TEXT turns a number or date into text in the format you choose, such as "$1,250.00" or "March 2026". You need it whenever you join numbers or dates with words, because joining drops the cell's formatting and shows the raw stored value.
- Available in
- Every current version
- Category
- Text functions
- Returns
- Text that looks like the formatted value. It can't be summed or used in date math.
- Google Sheets
- Available in Google Sheets with the same arguments.
Syntax
=TEXT(value, format_text)
| Argument | Required? | What it is |
|---|---|---|
value | Required | The number, date or cell to format. |
format_text | Required | A format code in quotes, using the same codes as Format Cells: "$#,##0.00", "0.0%", "mmm d, yyyy", "dddd". |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | ="Total: "&TEXT(B2,"$#,##0.00") | Total: $1,234.50 | Keeps the currency format inside a sentence |
| D3 | ="Due "&TEXT(A3,"mmm d, yyyy") | Due Nov 30, 2026 | A readable date; without TEXT it would show 46356 |
| D4 | =TEXT(A4,"dddd") | Saturday | The day of the week as a word |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Order date | Total | Margin | Result |
| 3/7/2026 | $1,234.50 | 25.6% | Total: $1,234.50 |
| 11/30/2026 | $89.00 | 31.0% | Due Nov 30, 2026 |
| 7/4/2026 | $15,000.00 | 8.2% | Saturday |
Practice TEXT
From lesson 17, DATE, YEAR, MONTH & TEXT. Graded instantly, then re-checked on changed data.
Month labels
D2, show the three-letter month name (Jan, Feb…) for each invoice date. Fill down to D6.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| The results won't add up | TEXT returns text, so SUM ignores it. Keep the number in its own cell for math and use TEXT only for display. |
| The format code gives the wrong result | Codes go in quotes, and m means month in a date but minutes after h. Copy the code from Format Cells > Custom to be sure it's right. |
| Codes break in another language version | Format codes follow the language of Excel, so "yyyy" may not work for a colleague using, for example, German Excel. Keep shared reports to formatted cells where you can. |
Tips
- Pad numbers with leading zeros using a code like "00000", for ZIP codes and IDs.
- For labels, "mmm d" gives Mar 7 and "dddd, mmmm d" gives Saturday, March 7.
Questions
Why does my date show as a number when I join it with text?
Dates are stored as serial numbers, and joining with & uses the stored value. Wrap the date in TEXT: ="Due "&TEXT(A2,"mmm d, yyyy").
How do I add leading zeros in Excel?
=TEXT(A2,"00000") pads a number to five digits, so 412 becomes 00412. The result is text, which is what you want for ZIP codes and IDs.
Lessons that use TEXT
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.