Currency with thousands separator
| A | |
|---|---|
| 1 | Amount |
| 2 | 1234.5 |
Formula: C1: =TEXT(A2,"$#,##0.00")
Result: $1,234.50
#,##0 adds the thousands separator, 0.00 forces two decimals.
Google Sheets function
TEXT in Google Sheets
TEXT format codes in Google Sheets are read according to the spreadsheet locale (File, Settings), which affects separators and month names.
Syntax (same in Google Sheets)
TEXT(value, format_text)If a code gives a different layout from the one shown here, check that setting first.
For a fuller explanation of the arguments and common errors, see the TEXT Excel guide.
Worked examples in Google Sheets
Readable date
| A | |
|---|---|
| 1 | Date |
| 2 | 3/9/2024 |
Formula: C1: =TEXT(A2,"mmm d, yyyy")
Result: Mar 9, 2024
mmm is the short month name, d the day, yyyy the four-digit year. Use "dddd" for the weekday name.
Leading zeros
| A | |
|---|---|
| 1 | Invoice |
| 2 | 42 |
Formula: C1: =TEXT(A2,"00000")
Result: 00042
Each 0 is a mandatory digit, so 42 is padded to five characters.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in D2: =TEXT(A2,"#,##0.00")
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Number | Step | Result | |
| 2 | 1234.567 | 50 | 1,234.57 | |
| 3 | 17 | 5 | ||
| 4 | -4.7 | 12 | ||
| 5 | 144 | 9 | ||
| 6 | 2.5 | 3 | ||
| 7 | 50 | 12 |
Result in D2: 1,234.57
Frequently asked questions
- Why does TEXT show a different date or number layout in Google Sheets?
- The format code is interpreted in the spreadsheet locale, so decimal marks, thousands separators and month names follow File, Settings, Locale.
Google Sheets is used here only as a descriptive product name. FormulaCamp is not affiliated with Google.