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.