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 .
Explanation
Note: if a keyword appears more than once in a given cell, it will only be counted once. In other words, the formula only counts instances of different keywords.
The core of this formula is the ISNUMBER + SEARCH approach to finding text in a cell, which is explained in more detail here . In this case, we are looking in each cell for all words in the named range “keywords” (E5:E9). We do this by passing the range into SEARCH as the find_text argument. Because we pass in an array of 5 items:
{"green";"orange";"white";"blue";"pink"}
we get an array of 5 items back as a result:
{#VALUE!;#VALUE!;1;#VALUE!;14}
Numbers correspond to matches, and the #VALUE! error means no match was found. In this case, because we don’t care where the text was found in the cell, we use ISNUMBER to convert the array into TRUE and FALSE values:
{FALSE;FALSE;TRUE;FALSE;TRUE}
And the double negative (–) to change these into 1s and zeros:
{0;0;1;0;1}
The SUMPRODUCT function then simply returns the sum of the array, 2 in this case.
Handling empty keywords
If the keyword range contains empty cells, the formula won’t work correctly, because the SEARCH function returns zero when looking for an empty string (""). To filter any empty cells in the keyword range, you can use the variation below:
{=SUMPRODUCT(--ISNUMBER(SEARCH(IF(keywords<>"",keywords),B5)))}
Note: this version an array formula and must be entered with control + shift + enter.
