Explanation
Normally, you want to maintain numeric values in Excel, because they can be used in formulas that perform numeric calculations. However, there are situations where converting numbers to text makes sense. One example is when you want to concatenate (join) a formatted number to text. For example, “Sales last year increased by over 15%”, where the number .15 has been formatted with a percent symbol. Without the TEXT function, the number formatting will be stripped. Another example is when you want to perform a lookup on numbers using wildcards , which can’t be done with numeric values.
Convert with formatting
The TEXT function can be used to convert numbers to text using a given number format . In the example shown, the TEXT function is used to convert each number in column B to a text value using the formula and number shown in column F. The TEXT function accepts a number as the value argument, the number format to use as the format_text argument. The number format can include codes for dates, times, numbers, currency, percentage, and so on, as well as custom number formats . Notice the output from the TEXT function is a text value, which is left-aligned by default in Excel.
Convert without formatting
To convert a number in A1 to a text value without number formatting, you can concatenate the number to an empty string ("") like this:
=A1&"" // convert A1 to text
With the number 100 in cell A1, the result is “100”.
Explanation
In this example, the goal is to convert the text values seen in column B to the numeric values seen in column D. There are several ways to fix this problem in Excel, but this article focuses on a formula-based approach to convert text values to numbers. It also explains how to convert values in place with Paste Special, which does not require a formula.
The problem
Sometimes Excel will incorrectly evaluate a number as a text value. There are many reasons this might happen, including:
- The data has been imported from another application, and Excel has misinterpreted values.
- Data has been copy-pasted into Excel, and Excel does not recognize values as numbers.
- A user has added spaces, hyphens, commas, etc., to a number, changing it to text.
- The output of another formula is text, but expected to be a number.
Regardless of the cause, the problem is that a value that looks like a number but is actually text can’t be used in Excel like a true number. If you try to use it in a formula, you will typically get a #VALUE! error
Checking for numbers and text
In this example, the values in column B are “stored as text”. One way to understand how Excel is interpreting a value is to check the alignment. By default, Excel will align numeric values to the right side of a cell and text values to the left side of a cell, as you can see in the worksheet above. Alignment can be changed, however, so this is not a foolproof method of checking values.
Another way to check for numbers is to sum the values with the SUM function. The SUM function will ignore text values, so if the result is zero, you know that you have text values. You can also test values more explicitly with the ISNUMBER function or the ISTEXT function . Both formulas below will return either TRUE or FALSE for a value in cell A1:
=ISNUMBER(A1) // test for numbers
=ISTEXT(A1) // test for text
VALUE function
The VALUE function converts text that appears in a recognized format (i.e., a number, date, or time format) into a numeric value. When VALUE can successfully convert a text value to a number, it will just work, and VALUE will return the corresponding number. In cases where VALUE is not able to convert a text value to a number, it will return a #VALUE error.
Add zero (+0) trick
Another way to convert a text value to a number is to add zero with a formula like this:
=A1+0
The math operation forces Excel to try to convert the text value to a number. If the conversion succeeds, a number will be returned. If the conversion fails, the result will be a #VALUE error.
You will often see this trick in more advanced formulas because it’s short and a little more flexible than the VALUE function.
Add zero with Paste Special
If you want to perform an “in place” conversion of text to numbers without a formula, another solution is to add zero with a Paste Special command. This approach leaves the converted values in the same cells. The basic steps are as follows:
- Add a zero to an unused cell
- Copy the cell
- Select the values to convert
- Open Paste Special (Control + Option + V)
- Choose Values + Add
Video: How to perform in-place changes with Paste Special .
Strip all non-numeric characters
If a text value contains non-numeric characters like dashes, commas, punctuation, and so on, you’ll need to remove these characters before you can convert the value to a number. This calls for a more aggressive approach. If you are using a current version of Excel that has the REGEXREPLACE function , this formula will do it in one step:
=REGEXREPLACE(A1,"[^0-9]","")+0
In a nutshell, this formula uses regular expressions to replace every character that is not strictly a number with nothing. Then we add 0 to convert the text result into a numeric value. The nice thing about this approach is that it will handle all kinds of extra characters that are not numbers, both visible and non-visible. There is no need to identify and specify which characters to remove. For details, see Strip non-numeric characters .
In older versions of Excel without the REGEXREPLACE function, we need to take a more manual approach. The formulas in rows 12, 13, and 14 show how to use the LEFT and RIGHT functions to strip non-numeric characters from a text value before it’s converted to a number:
=LEFT(B12,2)+0 // left 2 characters
=RIGHT(B13,3)+0 // right 3 characters
=RIGHT(B14,4)+0 // right 4 characters
Each of these formulas extracts a specific number of characters from the left or right side of the text string. Then we add 0 to the result to make Excel convert the result to a numeric value.
To extract a number at a known location in the middle of a text string, use the MID function .
