D
dingy101
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
decimal such as 27.5" ?
excel 2003 SP2
Thanks
Gary
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Barb Reinhardt
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.
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.
B
Biff
Is the format ALWAYS the same?
In other words, will there ALWAYS be some feet, some inches and some
fraction of an inch?
Biff
In other words, will there ALWAYS be some feet, some inches and some
fraction of an inch?
Biff
D
dingy101
No,
2 1/2"
8"
10 1/2"
1'-7 3/8"
These are the different configurations.
Gary
2 1/2"
8"
10 1/2"
1'-7 3/8"
These are the different configurations.
Gary
B
Biff
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:
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
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
D
dingy101
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
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
P
Peo Sjoblom
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
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
R
Ron Coderre
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
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
B
Biff
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
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
R
Ron Coderre
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
=--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
B
Biff
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
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
R
Ron Coderre
There is a small typo (I think!) in the formula but it's superflous:
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
(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
B
Biff
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
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
R
Ron Coderre
Wanna swap files?<
Good idea!. Mine is on its way. Please send yours.
ronSKIPTHIScoderre AT bigfoot DOT com
***********
Best regards,
Ron
XL2002, WinXP-Pro
Good idea!. Mine is on its way. Please send yours.
ronSKIPTHIScoderre AT bigfoot DOT com
***********
Best regards,
Ron
XL2002, WinXP-Pro
B
Biff
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
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
R
Ron Coderre
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
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
B
Biff
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
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