Sometimes Excel ends up with text in a cell, when you really want a number. There are many reasons this might happen, and many ways to fix. This article describes a formula-based approach convert text values to numbers.
In this example, the values in column A are "stored as text". This means if you try to SUM column A, you'll get a result of zero.
The VALUE function will try to "coerce" a number stored as text to a true number. In simple cases, it will just work and you'll get a numeric result. If it doesn't work, you'll get a #VALUE error.
Add zero instead
Another common trick is to simply add zero to the text value with a formula like this:
This forces Excel to try and convert the text value to a number to handle the math operation. This has the same functionality as VALUE above. In the example shown, C7 uses this formula.
Stripping other characters
If a cell contains non-numeric characters like dashes, punctuation, and so on, you'll need to remove those characters before you can convert to numbers.
The formulas in C8 and C9 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. You can also use the MID function in more complicated situations. If you need to strip extra spaces or other non-printing characters, see the TRIM and CLEAN functions.
Finally, the SUBSTITUTE function will let you remove characters with "search and replace" type functionality.
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 (...
The formula runs from the inside out, with each SUBSTITUTE removing one character. The inner most SUBSTITUTE removes the left parentheses, and the result is handed to the next SUBSTITUTE, which removes the right parentheses, and so on. Whenever you...
The Excel VALUE function converts text that appears in a recognized format (i.e. a number, date, or time format) into a numeric value. Normally, the VALUE function is not needed in Excel, because Excel automatically converts text to numeric...
In this video you'll learn how to use multiple Excel functions to split, manipulate, and rejoin values inside a single formula.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.