From the Not Just Numbers blog:
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”.