View Single Post
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Convert feet/inches to decimal

Pretty close, Ron.

It crashes on entries like:

1/2"
3/8"
11/16"

It evaluates as the date serial number divided by 12:

1/2 = 38719/12 = 3226.583

I thought the OP wanted to convert to decimal inches. At least that's what
the example in the post indicates.

Biff

"Ron Coderre" wrote in message
...
It's not pretty, but near as I can tell, this formula handles all of your
posted examples:

For a value in A1
B1:
=--IF(ISERROR(FIND("'",A1)),0,IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"*'")0),"0
","")&LEFT(A1,FIND("'",A1)-1))+IF(ISERROR(FIND("""",A1)),0,--(IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"*
*/*")=0),"0
","")&SUBSTITUTE(MID(A1,IF(ISERROR(FIND("-",A1)),1,FIND("-",A1)+1),255),"""","")))/12

That works as long as "they" don't use fractions greater than 1 for FEET
(eg
1 1/2')

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"dingy101" wrote:

Hi,

1'-7 3/8"
1'-10"
1' - would be 1'-0", not 12"
2 1/2"
8"
1/2" - rare but possible, not in this set , but in future
10' -0 15/32" - also possible, 2 digits in feet and fraction in 32 nds
These items have been manually entered into a worksheet, came from
Autocad
drawings.
I have about six sheets with about 500 entries. I need to total items to
get
a figure for amount of steel to purchase.

I appreciate your efforts, I am suprised there is not a "set" way to do
this.

Thanks,
Gary

"Biff" wrote:

Ok, just to give you an idea of how "brutal" this is:

Just for this single format:

1'-7 3/8"

Requires this formula: (it could be slightly reduced as I wrote it with
the
intention of including all the possible formats)

=IF(ISNUMBER(FIND("'",A10)),LEFT(A10,FIND("'",A10)-1)*12,0)+IF(COUNT(FIND({"-","
"},A10))=2,MID(A10,FIND("-",A10)+1,FIND("
",A10)-1-FIND("-",A10)),0)+IF(COUNT(FIND({"
","/"},A10))=2,MID(A10,FIND("
",A10)+1,FIND("/",A10)-1-FIND("
",A10))/MID(A10,FIND("/",A10)+1,FIND("""",A10)-1-FIND("/",A10)),0)

A single formula that accounts for all the possible formats would be a
real
nightmare! Which leads me to ask about ALL THE POSSIBLE formats. You
listed
these:

2 1/2"
8"
10 1/2"
1'-7 3/8"

But I came up these:

1'-7 3/8"
1'-10"
1'
2 1/2"
8"
1/2"

How about if the measurement is one foot. Will that be entered as 12"
or 1'
(as I have listed above) ?

Where do these values come from? Are they manually entered by a user?
Are
they imported from some other app? Copy/pasted from a web site?

If you could enter each unit of measure into separate cells this would
be a
piece of cake!

If they are from some other app and you can't parse them out, then.....

I would recommend a separate formula breaking each measure into a
separate
cell then summing those cells together. For example, one cell to
calculate
the feet, one cell for the whole inches and one cell for the fractional
inches. The formulas for the inches would still be kind of long and
ugly!

There's just too many different formats to contend with!

Before I continue with this let me know what you think and let me know
ALL
the possible formats. It can be done but it ain't easy.

Biff

"dingy101" wrote in message
...
No,

2 1/2"
8"
10 1/2"
1'-7 3/8"

These are the different configurations.

Gary

"Biff" wrote:

Is the format ALWAYS the same?

In other words, will there ALWAYS be some feet, some inches and some
fraction of an inch?

Biff

"dingy101" wrote in message
...
Is there a way to convert a value in a cell that is 2'-3 1/2"
to a
decimal such as 27.5" ?

excel 2003 SP2

Thanks
Gary