Converting Text to Numbers in Excel 2

[reichertc]

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?

 

[Montemayor]

reichertc:

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

 

[reichertc]

I have thousands of them to do, and am not permitted to modify the source data. Besides, it does not matter what you do to the formatting of a cell that has text in it, it will always appear as text, and act as text in a formula.

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?

 

[johnwm]

Use the VLOOKUP function with your own table. If these are standard pipe sizes the table won't be that long. eg
[tt]
Col A Col B
1/2" 0.5
1" 1.0
1-1/2" 1.5

etc
[/tt]
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

 

[reichertc]

Thanks johnwm. I am smacking my forehead for not thinking of that. I am using that exact method in a number of other locations, but I never thought of using it here.

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

 

[joerd]

If you have a space instead of a dash in the number, Excel will recognize it as a fraction. So I used SUBSTITUTE to replace the dash with a space: [tt]SUBSTITUTE(G2,"-"," ")[/tt]
This fails in the case of sizes like 1/2", so if you prepend a 0 in such a case, it works: [tt]=IF(ISERROR(FIND("-",G2,1)),"0 "&G2,SUBSTITUTE(G2,"-"," "))[/tt]
(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.

 

[reichertc]

Thanks joerd. I had recognized that my formula for the 1.5" piping was working when the space was there instead of a dash. Never thought of putting a zero in front to force excel to realize I wasn't using a date.

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.

 

[jhardy1]

reichertc

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.