TEXT vs VALUE in Excel: Numbers to Text and Back

The short answer to TEXT vs VALUE in Excel: they are opposites. TEXT turns a number into text with the format you want, and VALUE turns text back into a number you can calculate with. You use TEXT to make numbers and dates look right inside sentences and labels, and VALUE to fix numbers that arrived as text from an import or a text formula. This guide compares both on the same data and shows the mistakes that cause wrong totals.

TEXT vs VALUE at a glance#

Think of TEXT as "make it look right" and VALUE as "make it count".

How the syntax differs#

TEXT takes two arguments, VALUE only one.

=TEXT(value, format_text)
=VALUE(text)

• value (TEXT) is the number or date to convert, usually a cell like C2.
• format_text (TEXT) is a format code in double quotes, such as "#,##0.00" for thousands separators and two decimals, or "dd mmm yyyy" for a date.
• text (VALUE) is the text to convert, such as "1250" or a cell holding number-like text.

The TEXT function guide and the VALUE function guide list the arguments. For a full list of format codes, see the guide to TEXT function formats.

Before you pick one, check what a cell actually holds. =ISNUMBER(A2) returns TRUE for a real number and =ISTEXT(A2) returns TRUE for text. Alignment is a quick visual clue too: by default, numbers sit on the right of the cell and text on the left, unless someone changed the alignment by hand.

The same data, both ways#

Here is a small product list. The weights came from a supplier file as text:

TEXT: put a price inside a sentence#

Joining a number to text with & loses its format:

="Price: "&C3

This shows Price: 940.5, with no currency sign and one decimal. Wrap the number in TEXT:

="Price: "&TEXT(C3,"$#,##0.00")

Now it shows Price: $940.50. The format code adds the dollar sign, the thousands separator and two decimal places.

TEXT: put a date inside a sentence#

Dates are stored as numbers, so joining one directly shows a number like 46096. TEXT fixes it:

="Ships on "&TEXT(D2,"dddd d mmm")

The result is Ships on Sunday 15 Mar. The guide to combining a date and text has more date patterns.

VALUE: turn text weights into numbers#

The weights can't be added, because "125 kg" is text. Take the digits with LEFT, then convert them with VALUE:

=VALUE(LEFT(B2,LEN(B2)-3))

LEN(B2) is 6 characters, so LEFT keeps the first 3: "125". VALUE turns that text into the number 125. Fill it down and =SUM(E2:E5) gives 450 kg. Without VALUE, LEFT returns text and SUM would give 0. That's the exercise on this page.

VALUE: fix imported numbers#

If a column of prices arrived as text, like "1250" with a small green triangle in the corner, =VALUE(C2) returns the number 1250. VALUE also reads formats such as "12%" (0.12) and "1,250" (1250). Some formats depend on your regional settings. The guide to numbers stored as text covers faster fixes for whole columns.

Both together: a sortable month key#

Sometimes you need both functions in one formula. To group dates by month with a number you can sort and compare, turn the date into text in year-month order, then back into a number:

=VALUE(TEXT(D2,"yyyymm"))

TEXT returns the text "202603", and VALUE turns it into the number 202603. Every date in March 2026 gets the same key, and keys sort in time order: 202512 comes before 202601. This is handy as a helper column for SUMIF totals by month.

When to use TEXT#

Use TEXT when the result is for people to read, not for further maths:

• Sentences and labels: "Total sales: $12,400.00" or "Report date: 01 Nov 2026".
• Leading zeros: =TEXT(A2,"00000") turns 42 into "00042" for product codes or ZIP codes.
• Pulling part of a date: =TEXT(D2,"mmmm") returns the month name, "March", for a chart label.
• Building IDs: ="INV-"&TEXT(A2,"0000") turns 7 into "INV-0007".

If you only want a cell to look different, don't use TEXT. Use Format Cells (Ctrl+1; Mac: ⌘1) instead. The cell stays a number, so it still adds up and sorts correctly.

When to use VALUE#

Use VALUE when number-like text needs to behave like a number:

• Imported data: numbers from a CSV, web page or another system that are stored as text.
• Extracted digits: numbers you pulled out of text with LEFT, RIGHT, MID or TEXTBEFORE, which always return text.
• Lookups that fail: if one table has 1001 as a number and the other as text "1001", XLOOKUP and VLOOKUP don't see them as equal. Converting with VALUE makes them match.
• Decimal commas: VALUE follows your computer's regional settings. If a file uses a different style, such as "1.250,50" for one thousand two hundred and fifty and a half, use NUMBERVALUE, which lets you name the separators: =NUMBERVALUE(A2,",",".") returns 1250.5. NUMBERVALUE is in Excel 2013 and later.
• Turning date text into dates: VALUE can read text like "2026-03-15" and return the date's serial number. For other date layouts, see how to convert text to a date.

Common mistakes with TEXT and VALUE#

These are the slips that most often cause wrong results:

1. Calculating with TEXT results. =SUM(F2:F5) over a column of TEXT results returns 0, because SUM ignores text. Keep the numbers in their own column and use TEXT only for display.
2. Sorting TEXT results. Text sorts character by character, so "1,250.00" comes before "940.50". Sort by the original numbers.
3. Using VALUE on text with extra characters. =VALUE("125 kg") returns #VALUE!. Strip the letters first with LEFT or SUBSTITUTE. The guide to the #VALUE! error covers other causes.
4. Hidden spaces. " 125" with a leading non-breaking space from a web page can make VALUE fail. Clean it with =VALUE(TRIM(SUBSTITUTE(A2,CHAR(160)," "))).
5. Format codes in another language. Date codes like "yyyy" are English codes. In some language versions of Excel, the letters differ, so a shared file may show odd results.
6. Forgetting the quotes. =TEXT(C2,0.00) doesn't work as expected. The format code must be text: "0.00".

Our recommendation#

Keep numbers as numbers for as long as possible. Use VALUE as soon as number-like text enters your sheet, so totals, lookups and sorting work. Use TEXT only at the last step, where a number or date goes into a sentence, label or code that people read. If you only want a different look, use Format Cells, not TEXT.

In short: VALUE to make it count, TEXT to make it readable.

Practise it#

Try the exercise on this page: pull the digits out of a text weight and turn them into a real number with VALUE. Then practise TEXT and VALUE in a live spreadsheet, where you format numbers for display and convert text back into numbers that add up.