Syntax
=TEXT(number, format)
number- The number or date to format.
format- The pattern, in quotes, such as "mmmm d, yyyy", "dddd" or "$#,##0.00".
TEXT function in Google Sheets
TEXT turns a number or a date into text with exactly the formatting you choose: month names, weekday names, currency, percentages, padded IDs. You need it whenever a formatted value goes into a sentence or label, because joining a date or a number with & otherwise drops its formatting.
Practice TEXT
Task 1 of 5
Show the full month name (like "March") of the first sale date. Answer in C2.
Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.
TEXT examples
Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Invoice | Date | Amount | Tax rate |
| 2 | 42 | 3/5/2026 | $1,234.50 | 7.5% |
| 3 | 7 | 11/20/2026 | $98.00 | 20% |
| 4 | 1305 | 7/1/2026 | $25,000.00 | 12.5% |
| Formula | Result | What it shows |
|---|---|---|
=TEXT(B2, "mmmm d, yyyy") | March 5, 2026 | A long date. |
=TEXT(B2, "dddd") | Thursday | The weekday name. |
=TEXT(B2, "mmm yy") | Mar 26 | A short month and year. |
=TEXT(B2, "yyyy-mm-dd") | 2026-03-05 | An ISO date, handy for file names and sortable text. |
="Due "&B2 | Due 46086 | Without TEXT, joining a date shows its serial number. |
="Due "&TEXT(B2, "mmm d") | Due Mar 5 | With TEXT, it reads properly. |
=TEXT(C2, "$#,##0.00") | $1,234.50 | Currency with a thousands separator. |
=TEXT(D2, "0.0%") | 7.5% | A percentage with one decimal. |
="INV-"&TEXT(A2, "00000") | INV-00042 | Zero-padded ID numbers. |
=TEXT(C2, "#,##0.00;(#,##0.00)") | 1,234.50 | A second section sets the format for negative numbers. |
TEXT format codes
| Code | Means | Example result |
|---|---|---|
d, dd | Day of the month, without or with a leading zero | 5, 05 |
ddd, dddd | Weekday name, short or full | Thu, Thursday |
m, mm | Month number | 3, 03 |
mmm, mmmm | Month name, short or full | Mar, March |
yy, yyyy | Year | 26, 2026 |
h, hh, mm, ss | Hours, minutes (an m after h means minutes), seconds | 2:05 |
AM/PM | 12-hour clock | 2:05 PM |
0 | A digit that is always shown (pads with zeros) | 000 → 007 |
# | A digit shown only if needed | #.## → 1.5 |
, | Thousands separator | #,##0 → 1,235 |
% | Multiply by 100 and add % | 0% → 8% |
; | Separates the positive and negative formats | 0;(0) → (5) |
Tips and common mistakes
- The result is text. It looks like a number or a date but won't sum, sort or compare as one. Keep the original value for calculations.
- To change how a cell looks without changing its value, use Format › Number instead of TEXT.
- "m" means month unless it follows an hour (h) or comes before seconds (ss), in which case it means minutes.
- Format codes in Sheets follow the spreadsheet's locale for month and day names.
Related functions
Learning step by step? TEXT is covered in Lesson 43: TEXT Function: Format Dates & Numbers of the free course.