Spotlight Excel Formula: Convert Text to Numbers

Numbers stored as text can present problems for lookup formulas and regular calculations. There are a few ways to convert Text to Numbers in Excel using a formula. We’ll look at three separate scenarios and see how we can use a formula to make things work the way they should.

Using Value in a VLookUp

In the example below, our formula should have returned Blue, but instead it gave us an #N/A.

Even though the entry in D1 appears right justified, it is a text value (numbers preceded by an apostrophe). The entries in A2 through A7 are actual numbers (values). Rather than manually fix the alphanumeric lookup values, we can simply enclose it in the Value function, which converts a number stored as text to value data.

Combo Cell with Left, Right or Mid

We can apply a similar solution when using Left, Right and Mid to extract data from a combined cell. For example, here we have used these functions to extract numbers in specific positions from the string, 32-ABCD1234V99. We did get the right numbers. But, since Left, Right and Mid are text functions, they return a text value.

If we simply wrap these formulas in the value function, we can get it to return actual values. Notice that the results were automatically right justified.

Simple Math: This multiplied by 1

If you have to convert a whole column of numbers stored as text to values, create a formula that multiples the cell contents by 1. Then, just copy it down. In some cases, you may need to perform a copy/paste values to actually change the value in the cell from a formula. Check out the Multiply by 1 worksheet in the sample workbook.

Finishing Touches

You may still need to format your results in the number format that makes the most sense. These methods may not return the right format. On the positive side, since it is possible that you will have mixed cells, some formatted as text and some formatted as numbers, none of the methods above will “break” a cell already formatted as a value.