The TEXT function
Updated · Works in: all current Excel versions, Google Sheets · Every example checked before release
Short answer
=TEXT(value, format) turns a number or date into formatted text, e.g. TEXT(A1,"$#,##0.00") → $1,234.50 or TEXT(B2,"mmm yyyy") → Mar 2026. Use it when joining numbers to text with &, since joining drops the cell's formatting.
When you join a number to text with &, its formatting disappears: $1,234.50 becomes 1234.5. TEXT fixes that by turning a value into formatted text, using the same codes as the Format Cells dialog.
Why you need it
Joining text uses the raw value. Edit this formula to see the difference with and without TEXT.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Product | Category | Qty | Price | Status | Summary |
| 2 | 1001 | 2 Mar 2026 | Mara Okafor | Monstera | Plants | 1 | $34.00 | Shipped | Order 1001: 34 |
| 3 | 1002 | 2 Mar 2026 | Jonas Lind | Terracotta pot | Pots | 3 | $12.50 | Shipped | Order 1002: 37.5 |
| 4 | 1003 | 3 Mar 2026 | Priya Raman | Snake plant | Plants | 2 | $22.00 | Shipped | Order 1003: 44 |
| 5 | 1004 | 4 Mar 2026 | Tomás Ruiz | Watering can | Tools | 1 | $18.00 | Pending | Order 1004: 18 |
| 6 | 1005 | 5 Mar 2026 | Elif Demir | Fern | Plants | 4 | $9.50 | Shipped | Order 1005: 38 |
| 7 | 1006 | 5 Mar 2026 | Mara Okafor | Potting mix | Soil | 2 | $7.25 | Returned | Order 1006: 14.5 |
| 8 | 1007 | 6 Mar 2026 | Kenji Mori | Pruning shears | Tools | 1 | $24.00 | Shipped | Order 1007: 24 |
| 9 | 1008 | 7 Mar 2026 | Ines Varga | Ceramic pot | Pots | 2 | $19.00 | Pending | Order 1008: 38 |
| 10 | 1009 | 8 Mar 2026 | Jonas Lind | Monstera | Plants | 1 | $34.00 | Shipped | Order 1009: 34 |
| 11 | 1010 | 9 Mar 2026 | Sofia Rossi | Fern | Plants | 6 | $9.50 | Shipped | Order 1010: 57 |
| 12 | 1011 | 10 Mar 2026 | Priya Raman | Potting mix | Soil | 5 | $7.25 | Shipped | Order 1011: 36.25 |
| 13 | 1012 | 11 Mar 2026 | Leo Brandt | Snake plant | Plants | 1 | $22.00 | Pending | Order 1012: 22 |
Money and thousands
"$#,##0.00" gives a dollar sign, thousands separators and two decimals. 0 means “always show a digit here”, # means “only if needed”.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Product | Category | Qty | Price | Status | Summary |
| 2 | 1001 | 2 Mar 2026 | Mara Okafor | Monstera | Plants | 1 | $34.00 | Shipped | Order 1001: $34.00 |
| 3 | 1002 | 2 Mar 2026 | Jonas Lind | Terracotta pot | Pots | 3 | $12.50 | Shipped | Order 1002: $37.50 |
| 4 | 1003 | 3 Mar 2026 | Priya Raman | Snake plant | Plants | 2 | $22.00 | Shipped | Order 1003: $44.00 |
| 5 | 1004 | 4 Mar 2026 | Tomás Ruiz | Watering can | Tools | 1 | $18.00 | Pending | Order 1004: $18.00 |
| 6 | 1005 | 5 Mar 2026 | Elif Demir | Fern | Plants | 4 | $9.50 | Shipped | Order 1005: $38.00 |
| 7 | 1006 | 5 Mar 2026 | Mara Okafor | Potting mix | Soil | 2 | $7.25 | Returned | Order 1006: $14.50 |
| 8 | 1007 | 6 Mar 2026 | Kenji Mori | Pruning shears | Tools | 1 | $24.00 | Shipped | Order 1007: $24.00 |
| 9 | 1008 | 7 Mar 2026 | Ines Varga | Ceramic pot | Pots | 2 | $19.00 | Pending | Order 1008: $38.00 |
| 10 | 1009 | 8 Mar 2026 | Jonas Lind | Monstera | Plants | 1 | $34.00 | Shipped | Order 1009: $34.00 |
| 11 | 1010 | 9 Mar 2026 | Sofia Rossi | Fern | Plants | 6 | $9.50 | Shipped | Order 1010: $57.00 |
| 12 | 1011 | 10 Mar 2026 | Priya Raman | Potting mix | Soil | 5 | $7.25 | Shipped | Order 1011: $36.25 |
| 13 | 1012 | 11 Mar 2026 | Leo Brandt | Snake plant | Plants | 1 | $22.00 | Pending | Order 1012: $22.00 |
Dates
Date codes: d/dd for the day, ddd/dddd for the weekday name, m/mm/mmm/mmmm for the month, yy/yyyy for the year.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Product | Category | Qty | Price | Status | Day |
| 2 | 1001 | 2 Mar 2026 | Mara Okafor | Monstera | Plants | 1 | $34.00 | Shipped | Monday 2 March |
| 3 | 1002 | 2 Mar 2026 | Jonas Lind | Terracotta pot | Pots | 3 | $12.50 | Shipped | Monday 2 March |
| 4 | 1003 | 3 Mar 2026 | Priya Raman | Snake plant | Plants | 2 | $22.00 | Shipped | Tuesday 3 March |
| 5 | 1004 | 4 Mar 2026 | Tomás Ruiz | Watering can | Tools | 1 | $18.00 | Pending | Wednesday 4 March |
| 6 | 1005 | 5 Mar 2026 | Elif Demir | Fern | Plants | 4 | $9.50 | Shipped | Thursday 5 March |
| 7 | 1006 | 5 Mar 2026 | Mara Okafor | Potting mix | Soil | 2 | $7.25 | Returned | Thursday 5 March |
| 8 | 1007 | 6 Mar 2026 | Kenji Mori | Pruning shears | Tools | 1 | $24.00 | Shipped | Friday 6 March |
| 9 | 1008 | 7 Mar 2026 | Ines Varga | Ceramic pot | Pots | 2 | $19.00 | Pending | Saturday 7 March |
| 10 | 1009 | 8 Mar 2026 | Jonas Lind | Monstera | Plants | 1 | $34.00 | Shipped | Sunday 8 March |
| 11 | 1010 | 9 Mar 2026 | Sofia Rossi | Fern | Plants | 6 | $9.50 | Shipped | Monday 9 March |
| 12 | 1011 | 10 Mar 2026 | Priya Raman | Potting mix | Soil | 5 | $7.25 | Shipped | Tuesday 10 March |
| 13 | 1012 | 11 Mar 2026 | Leo Brandt | Snake plant | Plants | 1 | $22.00 | Pending | Wednesday 11 March |
Percentages and leading zeros
"0%" and "0.0%" multiply by 100 and add the sign. "00000" pads with zeros, handy for codes and ZIP codes that must keep their leading 0.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Type | Budget | Actual | Used |
| 2 | Rent | Fixed | $1,200.00 | $1,200.00 | Rent: 100% used |
| 3 | Groceries | Variable | $450.00 | $512.40 | Groceries: 114% used |
| 4 | Utilities | Fixed | $160.00 | $148.75 | Utilities: 93% used |
| 5 | Transport | Variable | $120.00 | $96.50 | Transport: 80% used |
| 6 | Phone & internet | Fixed | $65.00 | $65.00 | Phone & internet: 100% used |
| 7 | Eating out | Variable | $150.00 | $203.10 | Eating out: 135% used |
| 8 | Subscriptions | Fixed | $38.00 | $44.97 | Subscriptions: 118% used |
| 9 | Gym | Fixed | $40.00 | $40.00 | Gym: 100% used |
| 10 | Gifts | Variable | $60.00 | $25.00 | Gifts: 42% used |
| 11 | Savings | Fixed | $300.00 | $300.00 | Savings: 100% used |
Check yourself
One real exercise from the “Text and dates” unit. Checked on our server; no account needed.Write an order summary like Order 1001: $34.00 in I2, with the order total (Qty × Price) formatted as money.
- Use TEXT
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Product | Category | Qty | Price | Status | Summary |
| 2 | 1001 | 2 Mar 2026 | Mara Okafor | Monstera | Plants | 1 | $34.00 | Shipped | |
| 3 | 1002 | 2 Mar 2026 | Jonas Lind | Terracotta pot | Pots | 3 | $12.50 | Shipped | |
| 4 | 1003 | 3 Mar 2026 | Priya Raman | Snake plant | Plants | 2 | $22.00 | Shipped | |
| 5 | 1004 | 4 Mar 2026 | Tomás Ruiz | Watering can | Tools | 1 | $18.00 | Pending | |
| 6 | 1005 | 5 Mar 2026 | Elif Demir | Fern | Plants | 4 | $9.50 | Shipped | |
| 7 | 1006 | 5 Mar 2026 | Mara Okafor | Potting mix | Soil | 2 | $7.25 | Returned | |
| 8 | 1007 | 6 Mar 2026 | Kenji Mori | Pruning shears | Tools | 1 | $24.00 | Shipped | |
| 9 | 1008 | 7 Mar 2026 | Ines Varga | Ceramic pot | Pots | 2 | $19.00 | Pending | |
| 10 | 1009 | 8 Mar 2026 | Jonas Lind | Monstera | Plants | 1 | $34.00 | Shipped | |
| 11 | 1010 | 9 Mar 2026 | Sofia Rossi | Fern | Plants | 6 | $9.50 | Shipped | |
| 12 | 1011 | 10 Mar 2026 | Priya Raman | Potting mix | Soil | 5 | $7.25 | Shipped | |
| 13 | 1012 | 11 Mar 2026 | Leo Brandt | Snake plant | Plants | 1 | $22.00 | Pending |
Common mistakes
Using TEXT on a number you still need to calculate with. Its result is text; keep the original number for maths.
Using
mmfor minutes and expecting months, or the other way round. In date formats,mmnext tohmeans minutes.Forgetting the quotes around the format code.
Questions
Why does TEXT give a different result on a colleague's computer?+
Some format codes depend on the language and region settings, especially date codes and decimal separators. Test shared files in the locale they'll be used in.
How do I join a whole range with commas?+
`TEXTJOIN(", ", TRUE, D2:D9)` joins every cell with a comma and a space; the TRUE skips blank cells. It needs Excel 2019 or later; Google Sheets has it too.
How do I turn the text back into a number?+
Use `VALUE(text)`, or better, keep the original number in its own cell and only use TEXT for display.
Reading is step one. Now make it stick.
Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.