When Excel stores a number as text, calculations and sorting may behave unexpectedly.
These formulas can convert text to numeric values, with examples to help you choose the right method for your data.
Why does Excel treat a number as text?
A cell can look like a number while containing text.
This can happen when data is imported from a website, CSV file, or another spreadsheet, or when a value has a leading apostrophe.
Extra spaces, currency symbols, and different decimal or thousands separators can also prevent Excel from recognizing a numeric value.
Text values often align to the left by default, while numbers align to the right, but formatting can change their appearance.
A quick check is =ISNUMBER(A2): it returns FALSE if A2 is text and TRUE if Excel recognizes it as a number.
What is the simplest Excel formula to convert text to a number?
For a clean text value such as 125 in A2, use =VALUE(A2).
The result is the numeric value 125, which Excel can use in arithmetic and numeric sorting.
In many cases, the double unary operator also converts numeric text: =--A2.
The two minus signs coerce the value to a number.
This formula is concise, but VALUE can be clearer to someone maintaining the workbook.
Both methods rely on Excel being able to interpret the text according to its regional settings.
If the text uses unfamiliar separators or includes unwanted characters, clean or parse it first.
Which formula should you use for common text-number problems?
- Clean numeric text: Use
=VALUE(A2)or=--A2. - Leading or trailing spaces: Use
=VALUE(TRIM(A2)). - Nonbreaking spaces from web data: Use
=VALUE(TRIM(SUBSTITUTE(A2,CHAR(160)," "))). - Known decimal and thousands separators: Use
=NUMBERVALUE(A2,".",",")for text such as1,234.56. - Currency symbol or other known characters: Remove them with
SUBSTITUTE, then convert withVALUE.
These formulas return a converted value in the formula cell; they do not change the original cell.
After checking the results, copy the converted range and use Paste Special > Values if you need to replace the original data.
How do you remove spaces before converting?
If A2 contains 125 with ordinary spaces, try =VALUE(TRIM(A2)).
TRIM removes leading and trailing spaces and reduces repeated internal spaces to single spaces.
For a number with no internal spaces, this typically gives 125.
Imported web data may contain a nonbreaking space, which ordinary TRIM does not remove.
CHAR(160) represents that character in common Windows-based Excel environments.
The formula =VALUE(TRIM(SUBSTITUTE(A2,CHAR(160)," "))) replaces it with a regular space before trimming and converting.
If the source contains other invisible characters, such as tabs or line breaks, inspect a sample and remove the specific characters with SUBSTITUTE before converting.
Avoid removing characters indiscriminately: a space can be a meaningful thousands separator depending on the source format.
How do you handle decimal and thousands separators?
Separator conventions differ.
One file may use 1,234.56, while another uses 1.234,56.
Excel's interpretation can depend on the computer's regional settings, so a formula that works in one locale may not work as expected in another.
NUMBERVALUE lets you specify the separators in the source text.
For the text 1,234.56 in A2, use =NUMBERVALUE(A2,".",",").
The first separator argument is the decimal separator; the second is the group, or thousands, separator.
For text written as 1.234,56, use =NUMBERVALUE(A2,",",".").
Both examples represent the numeric value 1234.56.
NUMBERVALUE is available in modern Excel versions, including Excel 2016 and later, Excel for the web, and Microsoft 365.
In older versions without this function, use VALUE after carefully replacing the source separators with those expected by that Excel installation.
How do you convert text that includes a currency symbol?
If A2 contains $125 and the symbol is consistent, remove it before conversion: =VALUE(SUBSTITUTE(A2,"$","")).
The result is the number 125.
For text that includes a space after the symbol, the same formula generally works because VALUE can interpret surrounding spaces; if needed, wrap the cleaned text in TRIM.
For example, to remove a known dollar sign and trim spaces, use =VALUE(TRIM(SUBSTITUTE(A2,"$",""))).
Replace "$" with the exact character used in your data.
A symbol or code that varies by row may require a more deliberate cleanup step, such as a helper column or a currency-specific parsing rule.
Do not remove punctuation without checking what it means.
A comma could mark thousands or decimals, and replacing it blindly can change the value.
How can you convert a whole range and preserve the results?
Enter the chosen formula in B2 for source cell A2, then fill it down alongside the data.
For example, if A2:A100 contains clean numeric text, enter =VALUE(A2) in B2 and copy it through B100.
The results in B2:B100 should be numeric values if every source entry is valid and compatible with your locale.
Check a few results with =ISNUMBER(B2) or compare a total such as =SUM(B2:B100) against an expected value.
If a formula returns #VALUE!, inspect the corresponding source cell for unsupported characters, inconsistent separators, or text that is not actually a number.
When the converted values are correct, copy B2:B100 and paste as values over the source range or into a destination range.
This removes the formulas while keeping their numeric results.
Apply the desired number format afterward; formatting changes how a number appears, while the conversion formula changes its underlying type.
What edge cases should you check?
- Leading apostrophe: A value such as
'125may be treated as text.
VALUEoften parses the visible numeric portion, but confirm withISNUMBER. - Blank cells: A conversion formula may return zero or an error depending on the function and input.
Use=IF(A2="","",VALUE(A2))when blank source cells should stay blank. - Dates and times: Excel stores dates and times as numbers, but date text depends on locale and date order.
Use a date-aware conversion approach when ambiguity matters. - Negative values in parentheses: Text such as
(125)may not convert directly.
Handle the parentheses explicitly if the source convention means a negative number. - Long identifiers: Account numbers, ZIP codes, and IDs may look numeric but should remain text, especially when leading zeros matter.
For a straightforward number stored as text, start with =VALUE(A2).
Add trimming, character replacement, or explicit separator arguments only when the source data calls for them, then verify that the results are numbers before replacing the original values.