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.

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.

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.

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.

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.

Convert Text-formatted numbers to proper numbers.