Convert feet/inches to decimal

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

OK, this can probably be broken down. Unfortunately, I don't have the time
to figure it all out so I'll give you the formulas I'd use
SEARCH, MID, LEFT, RIGHT, CONCATENATE (or you could use A1&A2) and maybe
VALUE.


"dingy101" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>>
>>
>>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >
> >>
> >>
> >>
>
>
>

I agree with Biff that this is brutal (wouldn't this be a good reason to
convert fully to metric, it's so much simpler?) <bg> Excel is not great for
parsing all different combinations the formulas tend to be monstrous
Here is some more on this subject

http://www.mvps.org/dmcritchie/excel/fractex1.htm

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"dingy101" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > 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
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > 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" <[email protected]> wrote in message
>> > >> news:[email protected]...
>> > >> > 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
>> > >> >
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > > 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" <[email protected]> wrote in message
> >> > >> news:[email protected]...
> >> > >> > 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
> >> > >> >
> >> > >>
> >> > >>
> >> > >>
> >> >
> >> >
> >> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> > > 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" <[email protected]> wrote in message
>> >> > >> news:[email protected]...
>> >> > >> > 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
>> >> > >> >
>> >> > >>
>> >> > >>
>> >> > >>
>> >> >
>> >> >
>> >> >
>>
>>
>>

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> > news:[email protected]...
> >> >> > > 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" <[email protected]> wrote in message
> >> >> > >> news:[email protected]...
> >> >> > >> > 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
> >> >> > >> >
> >> >> > >>
> >> >> > >>
> >> >> > >>
> >> >> >
> >> >> >
> >> >> >
> >>
> >>
> >>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in message
>> >> >> > news:[email protected]...
>> >> >> > > 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" <[email protected]> wrote in
>> >> >> > >> message
>> >> >> > >> news:[email protected]...
>> >> >> > >> > 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
>> >> >> > >> >
>> >> >> > >>
>> >> >> > >>
>> >> >> > >>
>> >> >> >
>> >> >> >
>> >> >> >
>> >>
>> >>
>> >>
>>
>>
>>

> 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" <[email protected]> wrote in message
> news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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" <[email protected]> wrote in message
> >> >> >> > news:[email protected]...
> >> >> >> > > 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" <[email protected]> wrote in
> >> >> >> > >> message
> >> >> >> > >> news:[email protected]...
> >> >> >> > >> > 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
> >> >> >> > >> >

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" <[email protected]> wrote in message
news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >> 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > 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" <[email protected]> wrote in
>> >> >> >> > message
>> >> >> >> > news:[email protected]...
>> >> >> >> > > 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" <[email protected]> wrote in
>> >> >> >> > >> message
>> >> >> >> > >> news:[email protected]...
>> >> >> >> > >> > 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
>> >> >> >> > >> >

Hi, Biff

I'm glad you finally got the formula to work. That wrapping issue can me a
real problem.

At first, I couldn't figure out why the formula needed a tweak...until I
looked back at the OP's request. Yup, the request was for a reult in inches,
not feet. (DUH!). Thanks for picking up on that.

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

XL2002, WinXP-Pro


"Biff" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >> 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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" <[email protected]> wrote in message
> >> >> >> news:[email protected]...
> >> >> >> > 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!

Hmmm....

Well, I replied to your email twice but apparently those replies bounced.

What was happening is that the formula was calculating the fractional inches
like this:

1/2"

1 divided by 2 divided by 12.

So, instead of getting a result of 0.5, the result was 0.041667.

Biff

"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
> Hi, Biff
>
> I'm glad you finally got the formula to work. That wrapping issue can me a
> real problem.
>
> At first, I couldn't figure out why the formula needed a tweak...until I
> looked back at the OP's request. Yup, the request was for a reult in
> inches,
> not feet. (DUH!). Thanks for picking up on that.
>
> ***********
> Best Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Biff" wrote:
>
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >> 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >> 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" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > 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" <[email protected]> wrote in message
>> >> >> >> news:[email protected]...
>> >> >> >> > 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!

So the below formula worked great for me with one minor exception. I sometimes have negative numbers. So my numbers are formated as: 2'-4 13/16" or sometimes as -2'-4 13/16" (as examples, we get plenty of 0'-6" or other variations as well, though the formula provided seems to cover these. Any modification that will allow a consistent formal to work regardless of - or +? I haven't tried it with this specific formula, but other similar ones that didn't work as well, I added "-1*" at the start of the formula to any cells that needed to be negative, but it was a manual process. Any assistance is greatly appreciated. Thank you.
OH I forgot to mention that I'm not an Excel guru by any means. I'm slightly smarter than the average bear, but the below formula is near Greek to me.

=--IF(ISERROR(FIND("'",E2)),0,IF(COUNTIF(E2,"*/*")*(COUNTIF(E2,"*'")>0),"0 ","")&LEFT(E2,FIND("'",E2)-1))+IF(ISERROR(FIND("""",E2)),0,--(IF(COUNTIF(E2,"*/*")*(COUNTIF(E2,"* */*")=0),"0 ","")&SUBSTITUTE(MID(E2,IF(ISERROR(FIND("-",E2)),1,FIND("-",E2)+1),255),"""","")))/12

you can reach me at robert DOT glover AT acibuilds DOT com but please also post here. Thank you.