masthead for advanced excel

Numbers displayed as text after LEFT, MID or RIGHT formula

Assuming that you have a range of cells containing containing both texts & numbers within the same cell. You would like to extract the numbers for some calculation, such as addition. When you apply the formulas LEFT, MID or RIGHT to the range of cells, Excel immediately present the extract numbers as text. In the example below, I have extracted numbers from Column A using formulas.

extracted number recognised as text

When we try to sum up the 3 numbers in (B2:B4), it returns zero. This is because Excel treated them as Text and not numbers.

text to numbers

Excel has a proper formula to convert such cases to Values. And being the friendly Excel we know, the name of the formula is�. VALUE. So placing the Text inside this formula will convert it to a Value. And my SUM in C5 is now able to calculate C2:C4.

Excel Value formula

You can of course put insert the Value formula directly into the cells in column B itself. Just place a VALUE formula in front of the formula we use to extract the numbers (after the equal sign) and close off the formula with a bracket at the end of the formula.

text to numbers using value

But I'm going to show you a slightly shorter method that will give you the same results. Simply add in "+0" after the formula and there you go.

Note that this method works only for cells which are not formatted as TEXT.

Add zero to convert text to number

Convert Text-formatted numbers to proper numbers.


New! Comments

Have your say about what you just read! Leave me a comment in the box below.