Excel Tip: Converting text to a number

Sometimes the information you need is not in the format you want it in.

In Excel, this problem often manifests itself as numbers formatted as text.

This is a problem, as when you try to do any kind of calculation with the number, you will get an error.

Excel's General number format will normally format a number that looks like a number, as a number, but this might not be the case for imported data, or if the number is part of a text string.

Fortunately, Excel (as usual) has an answer.

We can use the VALUE function to convert the text to a number.

So, for example, if cell A1 contains a troublesome number that is being read as text, that you wish to multiply by 2, but =A1*2 is returning an error, then:

=VALUE(A1)*2

should solve your problem.

A more common problem is where the number may be contained within a text string.

For example if A1 contained a number prefixed by a letter then we could use text manipulation to strip out the bit of text that contains the number - then use VALUE to convert it to a number.

So, if A1 contains text such as B230, and we wish to multiply the 230 by 2, we could use:

=VALUE(RIGHT(A1,LEN(A1)-1))*2

This earlier post explains the use of RIGHT and LEN. Essentially, RIGHT(text,x) returns the x rightmost characters from text, and len(text) returns the length of text (in characters). So, RIGHT(A1,LEN(A1)-1) returns all but one of the characters in A1, leaving out the leftmost character.

So, if A1=B230, then LEN(A1)-1=3, so RIGHT(A1,3)=230. This, however, is a text string, so we use the VALUE function to convert it to a number, before we multiply it by 2!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".