Three Ways To Covert A Number As Text To A Number

After viewing a video by another Excel expert (not that I’m an expert in any sense!), I tried to extract 3 digits from a text string and use it in another formula. 

I had a label ABC-789-DEF and I wanted to extract the 789 and use it in a VLOOKUP table as follows: 

Note that in cell A9 I get an #N/A (the formula for that cell is shown in B9).  The problem was the MID formula, which was returning the following result: 

Notice how it extracted the number I was looking for (“789”), but it was justified left. This should have been my indication that it was not formatted as a number but as text. That is why I got the #N/A result.

So, how do we fix that?  Easy.  There may be more, but three ways to convert a number as text to a true number are: 

1) put two minus signs in front of the formula
2) multiply the result times 1
3) use the VALUE function to convert the text number to a value 

As you can see, all are justified right and are usable in other functions.  I’ve changed the formula in A9 to use the “–” approach and it now gives the proper result! 

Happy Excelling! 

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply