If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Convert feet/inches to decimal
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Convert feet/inches to decimal

#1
February 17th 06, 12:56 AM posted to microsoft.public.excel.worksheet.functions
 dingy101 external usenet poster Posts: n/a
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

#2
February 17th 06, 01:59 AM posted to microsoft.public.excel.worksheet.functions
 Barb Reinhardt external usenet poster Posts: n/a
Convert feet/inches to decimal

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

#3
February 17th 06, 06:26 AM posted to microsoft.public.excel.worksheet.functions
 Biff external usenet poster Posts: n/a
Convert feet/inches to decimal

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
>

#4
February 17th 06, 12:55 PM posted to microsoft.public.excel.worksheet.functions
 dingy101 external usenet poster Posts: n/a
Convert feet/inches to decimal

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

>
>
>

#5
February 18th 06, 07:14 AM posted to microsoft.public.excel.worksheet.functions
 Biff external usenet poster Posts: n/a
Convert feet/inches to decimal

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

>>
>>
>>

#6
February 18th 06, 04:33 PM posted to microsoft.public.excel.worksheet.functions
 dingy101 external usenet poster Posts: n/a
Convert feet/inches to decimal

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

>
>
>

#7
February 18th 06, 07:12 PM posted to microsoft.public.excel.worksheet.functions
 Peo Sjoblom external usenet poster Posts: n/a
Convert feet/inches to decimal

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" > wrote in message
...
> 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
>> 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
>> >> >
>> >>
>> >>
>> >>

>>
>>
>>

#8
February 18th 06, 08:16 PM posted to microsoft.public.excel.worksheet.functions
 Ron Coderre external usenet poster Posts: n/a
Convert feet/inches to decimal

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

> >
> >
> >

#9
February 19th 06, 03:47 AM posted to microsoft.public.excel.worksheet.functions
 Biff external usenet poster Posts: n/a
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
>> 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
>> > 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
>> > >> >
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >

#10
February 19th 06, 04:07 AM posted to microsoft.public.excel.worksheet.functions
 Ron Coderre external usenet poster Posts: n/a
Convert feet/inches to decimal

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

>
>
>

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post convert imported data with . separator for decimal into , separato kris Excel Discussion (Misc queries) 1 September 8th 05 11:34 PM Convert decimal degree (lattitude/longitude) into Degree, Tim Ashcom Excel Discussion (Misc queries) 5 August 17th 05 04:53 PM Convert decimal hour into time format? ramdalen Excel Discussion (Misc queries) 2 June 20th 05 06:21 PM convert decimal number to time : convert 1,59 (minutes, dec) to m agenda9533 Excel Discussion (Misc queries) 8 January 20th 05 10:24 PM how to convert total hours (HH:MM) into decimal number Greg T Excel Worksheet Functions 1 November 17th 04 03:07 PM

All times are GMT +1. The time now is 02:09 PM.