## Converting Text to Numbers in Excel

## Converting Text to Numbers in Excel

(OP)

Alright. I have a list of pipe sizes, formatted as text. For example,

3"

6"

1-1/2"

1/2"

26"

I want to convert these to numbers. What I have done is:

1) Determine length of text string =LEN(...)

2) Strip off inch symbol =LEFT(...,LEN-1)

3) Convert to number =VALUE(...)

This is a little bit simplified because I also have some error checking in place.

Problem is that my pipe sizes like 1/2" are interpreted as dates and I end up getting a number like 37988 rather than 0.5. Anybody know how to get around this?

3"

6"

1-1/2"

1/2"

26"

I want to convert these to numbers. What I have done is:

1) Determine length of text string =LEN(...)

2) Strip off inch symbol =LEFT(...,LEN-1)

3) Convert to number =VALUE(...)

This is a little bit simplified because I also have some error checking in place.

Problem is that my pipe sizes like 1/2" are interpreted as dates and I end up getting a number like 37988 rather than 0.5. Anybody know how to get around this?

## RE: Converting Text to Numbers in Excel

Why not just highlight and go to Format - Cells - and select either Number, Fractions, Custom, etc. or whatever format you want for those cells? This is what I do all the time, and it has worked for me.

Art Montemayor

Spring, TX

## RE: Converting Text to Numbers in Excel

My real problem is that excel wants to treat 1/2, 3/4, and 1-1/2 as dates, and then when I convert to a value, it gives me the serial value of the date. I cannot even use the serial date number as an indication of what the original text number was, because each year the serial value of a two digit date will change. Excel seems to assume that a two digit date is the current year. So if I use these serial values in my formulas for purposes such as references or error checking, my formulas won't work next year. (It took me a while to figure out what was wrong this January...)

Anyone else?

## RE: Converting Text to Numbers in Excel

Col A Col B

1/2" 0.5

1" 1.0

1-1/2" 1.5

etc

Name the whole range using Insert|Name|Define, calling the range 'sizes'

Then use the formula thus:

=VLOOKUP(F1,sizes,2)

where F1 contains the string value

Good Luck

johnwm

## RE: Converting Text to Numbers in Excel

That has the added bonus of making error checking much easier.

## RE: Converting Text to Numbers in Excel

This fails in the case of sizes like 1/2", so if you prepend a 0 in such a case, it works: =IF(ISERROR(FIND("-",G2,1)),"0 "&G2,SUBSTITUTE(G2,"-"," "))

(in my case, I have the =LEFT(... formula in cell G2)

Cheers,

Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

## RE: Converting Text to Numbers in Excel

I still like the vlookup method, it is simpler for someone who didn't write the spreadsheet to understand. But I can think of several places where I can use your technique. (In this case I am dealing with nominal pipe sizes, so I do have a defined list of "acceptable" sizes.)

Anyone have any other ides out there for forcing excel not to assume that a number is really a date? This is one of my pet peeves.

## RE: Converting Text to Numbers in Excel

If you select the whole column BEFORE you start entering data, and then use "Format | Number | Text", when you type in:

1-1/2

that is exactly what you will see, NOT:

1/01/2002

which is what you get if you have set no particular format, because Excel tries to interpret your input as a date; or:

37257

if you have set "General" number formatting.

Hope this helps.