=TEXT(A2,"mmm d, yyyy") turns the date in A2 into the text Mar 15, 2026, and =TEXT(B2,"$#,##0.00") turns the number 1250.5 into $1,250.50. The second argument is a format code, in quotes, written with the same codes as Format Cells > Custom.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Amount | Date as text | Amount as text |
| 2 | 2026-03-15 | 1250.5 | Mar 15, 2026 | $1,250.50 |
| 3 | Sunday | 1,251 | ||
| 4 | 2026-03-15 | 1250.5 |
Change the date in A2 or the amount in B2 and the six texts follow. TEXT works in every Excel version and in Google Sheets.
Date format codes
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Result | Date | |
| 2 | d | 5 | 2026-03-05 | |
| 3 | dd | 05 | ||
| 4 | ddd | Thu | ||
| 5 | dddd | Thursday | ||
| 6 | mmm | Mar | ||
| 7 | mmmm | March | ||
| 8 | dd/mm/yyyy | 05/03/2026 |
danddd: the day, without and with a leading zero.dddanddddd: the weekday, short and long (Thu,Thursday). More on this on the weekday page.m,mm,mmm,mmmm: the month as 3, 03,Mar,March.yyandyyyy: the year as 26 or 2026.
Type another code in column A, such as mmmm yyyy or ddd d mmm, and column B shows it. Format codes depend on the language of your Excel: German Excel writes TT.MM.JJJJ where English Excel writes dd.mm.yyyy.
| A | B | |
|---|---|---|
| 1 | Date | Weekday |
| 2 | 2026-03-09 |
Your turn: In B2, return the full weekday name of the date in A2, such as Monday.
Number, currency and percent codes
| Code | 1234.567 becomes | Meaning |
|---|---|---|
0 | 1235 | whole number, rounded |
0.00 | 1234.57 | two decimals |
#,##0 | 1,235 | thousands separator |
$#,##0.00 | $1,234.57 | currency |
0% | 123457% | multiplied by 100, percent sign |
000000 | 001235 | at least 6 digits, leading zeros |
0 is a digit that is always shown, # a digit shown only when needed. Try them here:
| A | B | C | |
|---|---|---|---|
| 1 | Value | Code | Result |
| 2 | 1234.567 | 0.00 | 1234.57 |
| 3 | 0.256 | 0.0% | 25.6% |
| 4 | 42 | 000000 | 000042 |
| 5 | -1500 | #,##0;(#,##0) | (1,500) |
The last code has two sections separated by a semicolon: the first is for positive numbers, the second for negative ones, here in parentheses as accounts often show them. Padding with zeros, as in row 4, is covered on the leading zeros page.
| A | B | |
|---|---|---|
| 1 | Rate | Label |
| 2 | 0.125 |
Your turn: In B2, return the rate in A2 as text with one decimal and a percent sign, such as 12.5%.
Combine text with a formatted number or date
This is the most common reason to use TEXT. Joining a number with & drops its format, so the value is joined as it is stored:
| A | B | C | |
|---|---|---|---|
| 1 | Due | Amount | Message |
| 2 | 2026-04-30 | 1250.5 | Pay $1,250.50 by April 30 |
| 3 | Pay 1250.5 by 46142 |
C3 shows why: without TEXT the date joins as its serial number and the amount as a plain number. For times, use codes such as "h:mm AM/PM" or "hh:mm", and "[h]:mm" for durations over 24 hours. Next to h or s, mm means minutes, not months. Concatenate has more on joining text.
Common mistake: TEXT stops the math
TEXT returns text. It looks like a number, but SUM skips it and the cell is aligned like text:
| A | B | |
|---|---|---|
| 1 | Price | TEXT(price,"0.00") |
| 2 | 12.5 | 12.50 |
| 3 | 8 | 8.00 |
| 4 | 20.25 | 20.25 |
| 5 | 40.75 | 0 |
B5 totals 0. If you only want numbers to look different, keep them as numbers and change the cell format instead: select the cells, press Ctrl+1 (Cmd+1 on a Mac), and pick a format under Number or type the same code under Custom. The cell then looks the way TEXT would make it, and it still adds up.
Frequently Asked Questions
What does the TEXT function do in Excel?
It converts a number or date to text in the format you give: =TEXT(A2,"$#,##0.00") returns $1,250.50 for 1250.5. It is mostly used to keep a format when joining a value with other text.
How do I convert a date to text in Excel?
=TEXT(A2,"yyyy-mm-dd") returns 2026-03-15; "mmm d, yyyy" returns Mar 15, 2026; "dddd" returns the weekday name, such as Sunday.
Why can't I add up the results of TEXT?
TEXT returns text, and SUM skips text, so a column of TEXT results totals 0. Keep the numbers in their own cells and use Format Cells (Ctrl+1) to change how they look; use TEXT only for labels.
Why does my TEXT format code not work in a German or French Excel?
Date codes follow the language of Excel: German Excel writes the year as JJJJ and the day as TT, so =TEXT(A2;"TT.MM.JJJJ"). Codes copied from an English example show the letters literally.
