Search Not Just Numbers

Tuesday 12 May 2015

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".

2 comments:

  1. If I have a number as text, I find that performing an arithmetical operation on it will treat it as a number (also works for logicals, dates and will ignore leading/trailing spaces)
    and to get all-but-the-first-n-characters of a cell's contents, I find it easier to use MID(A1,n+1,99) [99 is an arbitrarily large number]

    so MID(A1,2,99)*2 works fine for me

    Jim

    ReplyDelete
    Replies
    1. Thanks Jim. Your solution with MID works just as well. I cover MID too in the post linked to above. Excel has got better at reading numbers, but I still find situations where VALUE is needed - usually with imported data.

      Delete