×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Converting Text to Numbers in Excel
2

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?

RE: Converting Text to Numbers in Excel

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

RE: Converting Text to Numbers in Excel

(OP)
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?

RE: Converting Text to Numbers in Excel

Use the VLOOKUP function with your own table. If these are standard pipe sizes the table won't be that long. eg

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

(OP)
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.

RE: Converting Text to Numbers in Excel

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: SUBSTITUTE(G2,"-"," ")
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

(OP)
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.

RE: Converting Text to Numbers in Excel

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close