OK!

We got this all straightened out!!!!!

Seems I was the victim of more than one instance of line wrap. Ron's formula
does work with a minor tweak:

=(--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)*12

I tried "cutting" that into chunks so the line wraps won't disguise the
needed spaces.

That works on the following formats:

1'-10 13/16"
1'-0 5/32"
1'-10"
1'-0"
2 1/2"
10"
1/2"

Biff

"Ron Coderre" wrote in message
...
Wanna swap files?<

Good idea!. Mine is on its way. Please send yours.

ronSKIPTHIScoderre AT bigfoot DOT com

***********
Best regards,
Ron

XL2002, WinXP-Pro


"Biff" wrote:

No change!

I thought that was a typo at first but before I edited it out I did try
it
as both:

(COUNTIF(A1,"**/*")=0)
(COUNTIF(A1,"* */*")=0)

Neither made a difference so that's when I edited it out.

Kind of strange why you would get:

1/2"____ 0.0416666666666667
3/8"____ 0.03125
11/16"__ 0.0572916666666667

Wanna swap files?

xlcanhelpatcomcastperiodnet

Remove the can and change the obvious.

Biff

"Ron Coderre" wrote in message
...
There is a small typo (I think!) in the formula but it's superflous:

(COUNTIF(A1,"**/*")=0)

That gets line wrapped (in OE) right between the 2 asterisks. I just
edited
one of them out.

Nope! That's not a typo in my formula, Biff. It's just an unfortunate
place
for the window to wrap. That part of the formula should be:
(COUNTIF(A1,"* */*")=0)

Notice the criteria is "asterisk_space_asterisk_slash_asterisk".

That's the part of the formula that tests if fractions have a space in
front
of them to prevent the kind of unwanted date conversion you described.
If
no,
the next part of the formula puts a zero and a space in front of the
fraction.

Try that and see if it works properly for you.

***********
Best regards,
Ron

XL2002, WinXP-Pro


"Biff" wrote:

Hmmm......

That's strange!

Here's a screencap:

http://img151.imageshack.us/img151/6421/feetinch4bz.jpg

Your formula is in column B. I put another formula in column C to
convert
to
decimal inches.

There is a small typo (I think!) in the formula but it's superflous:

(COUNTIF(A1,"**/*")=0)

That gets line wrapped (in OE) right between the 2 asterisks. I just
edited
one of them out.

???

Biff

"Ron Coderre" wrote in message
...
Hey, Biff...I'm a bit puzzled.

=--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

When I use that formula for those values I get:
1/2"____ 0.0416666666666667
3/8"____ 0.03125
11/16"__ 0.0572916666666667

What am I missing?

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

XL2002, WinXP-Pro


"Biff" wrote:

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