formulastreak

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.

Leaf & Loam orders · edit I2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultI2 = Order 1001: 34
ABCDEFGHI
1OrderDateCustomerProductCategoryQtyPriceStatusSummary
210012 Mar 2026Mara OkaforMonsteraPlants1$34.00ShippedOrder 1001: 34
310022 Mar 2026Jonas LindTerracotta potPots3$12.50ShippedOrder 1002: 37.5
410033 Mar 2026Priya RamanSnake plantPlants2$22.00ShippedOrder 1003: 44
510044 Mar 2026Tomás RuizWatering canTools1$18.00PendingOrder 1004: 18
610055 Mar 2026Elif DemirFernPlants4$9.50ShippedOrder 1005: 38
710065 Mar 2026Mara OkaforPotting mixSoil2$7.25ReturnedOrder 1006: 14.5
810076 Mar 2026Kenji MoriPruning shearsTools1$24.00ShippedOrder 1007: 24
910087 Mar 2026Ines VargaCeramic potPots2$19.00PendingOrder 1008: 38
1010098 Mar 2026Jonas LindMonsteraPlants1$34.00ShippedOrder 1009: 34
1110109 Mar 2026Sofia RossiFernPlants6$9.50ShippedOrder 1010: 57
12101110 Mar 2026Priya RamanPotting mixSoil5$7.25ShippedOrder 1011: 36.25
13101211 Mar 2026Leo BrandtSnake plantPlants1$22.00PendingOrder 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”.

Leaf & Loam orders · edit I2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultI2 = Order 1001: $34.00
ABCDEFGHI
1OrderDateCustomerProductCategoryQtyPriceStatusSummary
210012 Mar 2026Mara OkaforMonsteraPlants1$34.00ShippedOrder 1001: $34.00
310022 Mar 2026Jonas LindTerracotta potPots3$12.50ShippedOrder 1002: $37.50
410033 Mar 2026Priya RamanSnake plantPlants2$22.00ShippedOrder 1003: $44.00
510044 Mar 2026Tomás RuizWatering canTools1$18.00PendingOrder 1004: $18.00
610055 Mar 2026Elif DemirFernPlants4$9.50ShippedOrder 1005: $38.00
710065 Mar 2026Mara OkaforPotting mixSoil2$7.25ReturnedOrder 1006: $14.50
810076 Mar 2026Kenji MoriPruning shearsTools1$24.00ShippedOrder 1007: $24.00
910087 Mar 2026Ines VargaCeramic potPots2$19.00PendingOrder 1008: $38.00
1010098 Mar 2026Jonas LindMonsteraPlants1$34.00ShippedOrder 1009: $34.00
1110109 Mar 2026Sofia RossiFernPlants6$9.50ShippedOrder 1010: $57.00
12101110 Mar 2026Priya RamanPotting mixSoil5$7.25ShippedOrder 1011: $36.25
13101211 Mar 2026Leo BrandtSnake plantPlants1$22.00PendingOrder 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.

Leaf & Loam orders · edit I2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultI2 = Monday 2 March
ABCDEFGHI
1OrderDateCustomerProductCategoryQtyPriceStatusDay
210012 Mar 2026Mara OkaforMonsteraPlants1$34.00ShippedMonday 2 March
310022 Mar 2026Jonas LindTerracotta potPots3$12.50ShippedMonday 2 March
410033 Mar 2026Priya RamanSnake plantPlants2$22.00ShippedTuesday 3 March
510044 Mar 2026Tomás RuizWatering canTools1$18.00PendingWednesday 4 March
610055 Mar 2026Elif DemirFernPlants4$9.50ShippedThursday 5 March
710065 Mar 2026Mara OkaforPotting mixSoil2$7.25ReturnedThursday 5 March
810076 Mar 2026Kenji MoriPruning shearsTools1$24.00ShippedFriday 6 March
910087 Mar 2026Ines VargaCeramic potPots2$19.00PendingSaturday 7 March
1010098 Mar 2026Jonas LindMonsteraPlants1$34.00ShippedSunday 8 March
1110109 Mar 2026Sofia RossiFernPlants6$9.50ShippedMonday 9 March
12101110 Mar 2026Priya RamanPotting mixSoil5$7.25ShippedTuesday 10 March
13101211 Mar 2026Leo BrandtSnake plantPlants1$22.00PendingWednesday 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.

March household budget · edit E2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultE2 = Rent: 100% used
ABCDE
1CategoryTypeBudgetActualUsed
2RentFixed$1,200.00$1,200.00Rent: 100% used
3GroceriesVariable$450.00$512.40Groceries: 114% used
4UtilitiesFixed$160.00$148.75Utilities: 93% used
5TransportVariable$120.00$96.50Transport: 80% used
6Phone & internetFixed$65.00$65.00Phone & internet: 100% used
7Eating outVariable$150.00$203.10Eating out: 135% used
8SubscriptionsFixed$38.00$44.97Subscriptions: 118% used
9GymFixed$40.00$40.00Gym: 100% used
10GiftsVariable$60.00$25.00Gifts: 42% used
11SavingsFixed$300.00$300.00Savings: 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
Select I2 and type = to start its formula (in the cell or the formula bar)
ABCDEFGHI
1OrderDateCustomerProductCategoryQtyPriceStatusSummary
210012 Mar 2026Mara OkaforMonsteraPlants1$34.00Shipped
310022 Mar 2026Jonas LindTerracotta potPots3$12.50Shipped
410033 Mar 2026Priya RamanSnake plantPlants2$22.00Shipped
510044 Mar 2026Tomás RuizWatering canTools1$18.00Pending
610055 Mar 2026Elif DemirFernPlants4$9.50Shipped
710065 Mar 2026Mara OkaforPotting mixSoil2$7.25Returned
810076 Mar 2026Kenji MoriPruning shearsTools1$24.00Shipped
910087 Mar 2026Ines VargaCeramic potPots2$19.00Pending
1010098 Mar 2026Jonas LindMonsteraPlants1$34.00Shipped
1110109 Mar 2026Sofia RossiFernPlants6$9.50Shipped
12101110 Mar 2026Priya RamanPotting mixSoil5$7.25Shipped
13101211 Mar 2026Leo BrandtSnake plantPlants1$22.00Pending

Common mistakes

  • Using TEXT on a number you still need to calculate with. Its result is text; keep the original number for maths.

  • Using mm for minutes and expecting months, or the other way round. In date formats, mm next to h means 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.

Practise it now