Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 nightmare! Which leads me to ask about ALL THE POSSIBLE formats. You listed these: 2 1/2" 8" 10 1/2" 1'-7 3/8" But I came up these: 1'-7 3/8" 1'-10" 1' 2 1/2" 8" 1/2" How about if the measurement is one foot. Will that be entered as 12" or 1' (as I have listed above) ? Where do these values come from? Are they manually entered by a user? Are they imported from some other app? Copy/pasted from a web site? If you could enter each unit of measure into separate cells this would be a piece of cake! If they are from some other app and you can't parse them out, then..... I would recommend a separate formula breaking each measure into a separate cell then summing those cells together. For example, one cell to calculate the feet, one cell for the whole inches and one cell for the fractional inches. The formulas for the inches would still be kind of long and ugly! There's just too many different formats to contend with! Before I continue with this let me know what you think and let me know ALL the possible formats. It can be done but it ain't easy. Biff "dingy101" wrote in message ... No, 2 1/2" 8" 10 1/2" 1'-7 3/8" These are the different configurations. Gary "Biff" wrote: Is the format ALWAYS the same? In other words, will there ALWAYS be some feet, some inches and some fraction of an inch? Biff "dingy101" wrote in message ... Is there a way to convert a value in a cell that is 2'-3 1/2" to a decimal such as 27.5" ? excel 2003 SP2 Thanks Gary |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 nightmare! Which leads me to ask about ALL THE POSSIBLE formats. You listed these: 2 1/2" 8" 10 1/2" 1'-7 3/8" But I came up these: 1'-7 3/8" 1'-10" 1' 2 1/2" 8" 1/2" How about if the measurement is one foot. Will that be entered as 12" or 1' (as I have listed above) ? Where do these values come from? Are they manually entered by a user? Are they imported from some other app? Copy/pasted from a web site? If you could enter each unit of measure into separate cells this would be a piece of cake! If they are from some other app and you can't parse them out, then..... I would recommend a separate formula breaking each measure into a separate cell then summing those cells together. For example, one cell to calculate the feet, one cell for the whole inches and one cell for the fractional inches. The formulas for the inches would still be kind of long and ugly! There's just too many different formats to contend with! Before I continue with this let me know what you think and let me know ALL the possible formats. It can be done but it ain't easy. Biff "dingy101" wrote in message ... No, 2 1/2" 8" 10 1/2" 1'-7 3/8" These are the different configurations. Gary "Biff" wrote: Is the format ALWAYS the same? In other words, will there ALWAYS be some feet, some inches and some fraction of an inch? Biff "dingy101" wrote in message ... Is there a way to convert a value in a cell that is 2'-3 1/2" to a decimal such as 27.5" ? excel 2003 SP2 Thanks Gary |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 nightmare! Which leads me to ask about ALL THE POSSIBLE formats. You listed these: 2 1/2" 8" 10 1/2" 1'-7 3/8" But I came up these: 1'-7 3/8" 1'-10" 1' 2 1/2" 8" 1/2" How about if the measurement is one foot. Will that be entered as 12" or 1' (as I have listed above) ? Where do these values come from? Are they manually entered by a user? Are they imported from some other app? Copy/pasted from a web site? If you could enter each unit of measure into separate cells this would be a piece of cake! If they are from some other app and you can't parse them out, then..... I would recommend a separate formula breaking each measure into a separate cell then summing those cells together. For example, one cell to calculate the feet, one cell for the whole inches and one cell for the fractional inches. The formulas for the inches would still be kind of long and ugly! There's just too many different formats to contend with! Before I continue with this let me know what you think and let me know ALL the possible formats. It can be done but it ain't easy. Biff "dingy101" wrote in message ... No, 2 1/2" 8" 10 1/2" 1'-7 3/8" These are the different configurations. Gary "Biff" wrote: Is the format ALWAYS the same? In other words, will there ALWAYS be some feet, some inches and some fraction of an inch? Biff "dingy101" wrote in message ... Is there a way to convert a value in a cell that is 2'-3 1/2" to a decimal such as 27.5" ? excel 2003 SP2 Thanks Gary |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 nightmare! Which leads me to ask about ALL THE POSSIBLE formats. You listed these: 2 1/2" 8" 10 1/2" 1'-7 3/8" But I came up these: 1'-7 3/8" 1'-10" 1' 2 1/2" 8" 1/2" How about if the measurement is one foot. Will that be entered as 12" or 1' (as I have listed above) ? Where do these values come from? Are they manually entered by a user? Are they imported from some other app? Copy/pasted from a web site? If you could enter each unit of measure into separate cells this would be a piece of cake! If they are from some other app and you can't parse them out, then..... I would recommend a separate formula breaking each measure into a separate cell then summing those cells together. For example, one cell to calculate the feet, one cell for the whole inches and one cell for the fractional inches. The formulas for the inches would still be kind of long and ugly! There's just too many different formats to contend with! Before I continue with this let me know what you think and let me know ALL the possible formats. It can be done but it ain't easy. Biff "dingy101" wrote in message ... No, 2 1/2" 8" 10 1/2" 1'-7 3/8" These are the different configurations. Gary "Biff" wrote: Is the format ALWAYS the same? In other words, will there ALWAYS be some feet, some inches and some fraction of an inch? Biff "dingy101" wrote in message ... Is there a way to convert a value in a cell that is 2'-3 1/2" to a decimal such as 27.5" ? excel 2003 SP2 Thanks Gary |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert feet/inches to decimal
Pretty close, Ron.
It crashes on entries like: 1/2" 3/8" 11/16" It evaluates as the date serial number divided by 12: 1/2 = 38719/12 = 3226.583 I thought the OP wanted to convert to decimal inches. At least that's what the example in the post indicates. Biff "Ron Coderre" wrote in message ... It's not pretty, but near as I can tell, this formula handles all of your posted examples: For a value in A1 B1: =--IF(ISERROR(FIND("'",A1)),0,IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"*'")0),"0 ","")&LEFT(A1,FIND("'",A1)-1))+IF(ISERROR(FIND("""",A1)),0,--(IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"* */*")=0),"0 ","")&SUBSTITUTE(MID(A1,IF(ISERROR(FIND("-",A1)),1,FIND("-",A1)+1),255),"""","")))/12 That works as long as "they" don't use fractions greater than 1 for FEET (eg 1 1/2') Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "dingy101" wrote: Hi, 1'-7 3/8" 1'-10" 1' - would be 1'-0", not 12" 2 1/2" 8" 1/2" - rare but possible, not in this set , but in future 10' -0 15/32" - also possible, 2 digits in feet and fraction in 32 nds These items have been manually entered into a worksheet, came from Autocad drawings. I have about six sheets with about 500 entries. I need to total items to get a figure for amount of steel to purchase. I appreciate your efforts, I am suprised there is not a "set" way to do this. Thanks, Gary "Biff" wrote: Ok, just to give you an idea of how "brutal" this is: Just for this single format: 1'-7 3/8" Requires this formula: (it could be slightly reduced as I wrote it with the intention of including all the possible formats) =IF(ISNUMBER(FIND("'",A10)),LEFT(A10,FIND("'",A10)-1)*12,0)+IF(COUNT(FIND({"-"," "},A10))=2,MID(A10,FIND("-",A10)+1,FIND(" ",A10)-1-FIND("-",A10)),0)+IF(COUNT(FIND({" ","/"},A10))=2,MID(A10,FIND(" ",A10)+1,FIND("/",A10)-1-FIND(" ",A10))/MID(A10,FIND("/",A10)+1,FIND("""",A10)-1-FIND("/",A10)),0) A single formula that accounts for all the possible formats would be a real nightmare! Which leads me to ask about ALL THE POSSIBLE formats. You listed these: 2 1/2" 8" 10 1/2" 1'-7 3/8" But I came up these: 1'-7 3/8" 1'-10" 1' 2 1/2" 8" 1/2" How about if the measurement is one foot. Will that be entered as 12" or 1' (as I have listed above) ? Where do these values come from? Are they manually entered by a user? Are they imported from some other app? Copy/pasted from a web site? If you could enter each unit of measure into separate cells this would be a piece of cake! If they are from some other app and you can't parse them out, then..... I would recommend a separate formula breaking each measure into a separate cell then summing those cells together. For example, one cell to calculate the feet, one cell for the whole inches and one cell for the fractional inches. The formulas for the inches would still be kind of long and ugly! There's just too many different formats to contend with! Before I continue with this let me know what you think and let me know ALL the possible formats. It can be done but it ain't easy. Biff "dingy101" wrote in message ... No, 2 1/2" 8" 10 1/2" 1'-7 3/8" These are the different configurations. Gary "Biff" wrote: Is the format ALWAYS the same? In other words, will there ALWAYS be some feet, some inches and some fraction of an inch? Biff "dingy101" wrote in message ... Is there a way to convert a value in a cell that is 2'-3 1/2" to a decimal such as 27.5" ? excel 2003 SP2 Thanks Gary |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 Autocad drawings. I have about six sheets with about 500 entries. I need to total items to get a figure for amount of steel to purchase. I appreciate your efforts, I am suprised there is not a "set" way to do this. Thanks, Gary "Biff" wrote: Ok, just to give you an idea of how "brutal" this is: Just for this single format: 1'-7 3/8" Requires this formula: (it could be slightly reduced as I wrote it with the intention of including all the possible formats) =IF(ISNUMBER(FIND("'",A10)),LEFT(A10,FIND("'",A10)-1)*12,0)+IF(COUNT(FIND({"-"," "},A10))=2,MID(A10,FIND("-",A10)+1,FIND(" ",A10)-1-FIND("-",A10)),0)+IF(COUNT(FIND({" ","/"},A10))=2,MID(A10,FIND(" ",A10)+1,FIND("/",A10)-1-FIND(" ",A10))/MID(A10,FIND("/",A10)+1,FIND("""",A10)-1-FIND("/",A10)),0) A single formula that accounts for all the possible formats would be a real nightmare! Which leads me to ask about ALL THE POSSIBLE formats. You listed these: 2 1/2" 8" 10 1/2" 1'-7 3/8" But I came up these: 1'-7 3/8" 1'-10" 1' 2 1/2" 8" 1/2" How about if the measurement is one foot. Will that be entered as 12" or 1' (as I have listed above) ? Where do these values come from? Are they manually entered by a user? Are they imported from some other app? Copy/pasted from a web site? If you could enter each unit of measure into separate cells this would be a piece of cake! If they are from some other app and you can't parse them out, then..... I would recommend a separate formula breaking each measure into a separate cell then summing those cells together. For example, one cell to calculate the feet, one cell for the whole inches and one cell for the fractional inches. The formulas for the inches would still be kind of long and ugly! There's just too many different formats to contend with! Before I continue with this let me know what you think and let me know ALL the possible formats. It can be done but it ain't easy. Biff "dingy101" wrote in message ... No, 2 1/2" 8" 10 1/2" 1'-7 3/8" These are the different configurations. Gary "Biff" wrote: Is the format ALWAYS the same? In other words, will there ALWAYS be some feet, some inches and some fraction of an inch? Biff "dingy101" wrote in message ... Is there a way to convert a value in a cell that is 2'-3 1/2" to a decimal such as 27.5" ? excel 2003 SP2 Thanks Gary |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert feet/inches to decimal
Hmmm......
That's strange! Here's a screencap: http://img151.imageshack.us/img151/6421/feetinch4bz.jpg Your formula is in column B. I put another formula in column C to convert to decimal inches. There is a small typo (I think!) in the formula but it's superflous: (COUNTIF(A1,"**/*")=0) That gets line wrapped (in OE) right between the 2 asterisks. I just edited one of them out. ??? Biff "Ron Coderre" wrote in message ... Hey, Biff...I'm a bit puzzled. =--IF(ISERROR(FIND("'",A1)),0,IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"*'")0),"0 ","")&LEFT(A1,FIND("'",A1)-1))+IF(ISERROR(FIND("""",A1)),0,--(IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"* */*")=0),"0 ","")&SUBSTITUTE(MID(A1,IF(ISERROR(FIND("-",A1)),1,FIND("-",A1)+1),255),"""","")))/12 When I use that formula for those values I get: 1/2"____ 0.0416666666666667 3/8"____ 0.03125 11/16"__ 0.0572916666666667 What am I missing? *********** Regards, Ron XL2002, WinXP-Pro "Biff" wrote: Pretty close, Ron. It crashes on entries like: 1/2" 3/8" 11/16" It evaluates as the date serial number divided by 12: 1/2 = 38719/12 = 3226.583 I thought the OP wanted to convert to decimal inches. At least that's what the example in the post indicates. Biff "Ron Coderre" wrote in message ... It's not pretty, but near as I can tell, this formula handles all of your posted examples: For a value in A1 B1: =--IF(ISERROR(FIND("'",A1)),0,IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"*'")0),"0 ","")&LEFT(A1,FIND("'",A1)-1))+IF(ISERROR(FIND("""",A1)),0,--(IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"* */*")=0),"0 ","")&SUBSTITUTE(MID(A1,IF(ISERROR(FIND("-",A1)),1,FIND("-",A1)+1),255),"""","")))/12 That works as long as "they" don't use fractions greater than 1 for FEET (eg 1 1/2') Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "dingy101" wrote: Hi, 1'-7 3/8" 1'-10" 1' - would be 1'-0", not 12" 2 1/2" 8" 1/2" - rare but possible, not in this set , but in future 10' -0 15/32" - also possible, 2 digits in feet and fraction in 32 nds These items have been manually entered into a worksheet, came from Autocad drawings. I have about six sheets with about 500 entries. I need to total items to get a figure for amount of steel to purchase. I appreciate your efforts, I am suprised there is not a "set" way to do this. Thanks, Gary "Biff" wrote: Ok, just to give you an idea of how "brutal" this is: Just for this single format: 1'-7 3/8" Requires this formula: (it could be slightly reduced as I wrote it with the intention of including all the possible formats) =IF(ISNUMBER(FIND("'",A10)),LEFT(A10,FIND("'",A10)-1)*12,0)+IF(COUNT(FIND({"-"," "},A10))=2,MID(A10,FIND("-",A10)+1,FIND(" ",A10)-1-FIND("-",A10)),0)+IF(COUNT(FIND({" ","/"},A10))=2,MID(A10,FIND(" ",A10)+1,FIND("/",A10)-1-FIND(" ",A10))/MID(A10,FIND("/",A10)+1,FIND("""",A10)-1-FIND("/",A10)),0) A single formula that accounts for all the possible formats would be a real nightmare! Which leads me to ask about ALL THE POSSIBLE formats. You listed these: 2 1/2" 8" 10 1/2" 1'-7 3/8" But I came up these: 1'-7 3/8" 1'-10" 1' 2 1/2" 8" 1/2" How about if the measurement is one foot. Will that be entered as 12" or 1' (as I have listed above) ? Where do these values come from? Are they manually entered by a user? Are they imported from some other app? Copy/pasted from a web site? If you could enter each unit of measure into separate cells this would be a piece of cake! If they are from some other app and you can't parse them out, then..... I would recommend a separate formula breaking each measure into a separate cell then summing those cells together. For example, one cell to calculate the feet, one cell for the whole inches and one cell for the fractional inches. The formulas for the inches would still be kind of long and ugly! There's just too many different formats to contend with! Before I continue with this let me know what you think and let me know ALL the possible formats. It can be done but it ain't easy. Biff "dingy101" wrote in message ... No, 2 1/2" 8" 10 1/2" 1'-7 3/8" These are the different configurations. Gary "Biff" wrote: Is the format ALWAYS the same? In other words, will there ALWAYS be some feet, some inches and some fraction of an inch? Biff "dingy101" wrote in message ... Is there a way to convert a value in a cell that is 2'-3 1/2" to a decimal such as 27.5" ? excel 2003 SP2 Thanks Gary |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert feet/inches to decimal
There is a small typo (I think!) in the formula but it's superflous:
(COUNTIF(A1,"**/*")=0) That gets line wrapped (in OE) right between the 2 asterisks. I just edited one of them out. Nope! That's not a typo in my formula, Biff. It's just an unfortunate place for the window to wrap. That part of the formula should be: (COUNTIF(A1,"* */*")=0) Notice the criteria is "asterisk_space_asterisk_slash_asterisk". That's the part of the formula that tests if fractions have a space in front of them to prevent the kind of unwanted date conversion you described. If no, the next part of the formula puts a zero and a space in front of the fraction. Try that and see if it works properly for you. *********** Best regards, Ron XL2002, WinXP-Pro "Biff" wrote: Hmmm...... That's strange! Here's a screencap: http://img151.imageshack.us/img151/6421/feetinch4bz.jpg Your formula is in column B. I put another formula in column C to convert to decimal inches. There is a small typo (I think!) in the formula but it's superflous: (COUNTIF(A1,"**/*")=0) That gets line wrapped (in OE) right between the 2 asterisks. I just edited one of them out. ??? Biff "Ron Coderre" wrote in message ... Hey, Biff...I'm a bit puzzled. =--IF(ISERROR(FIND("'",A1)),0,IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"*'")0),"0 ","")&LEFT(A1,FIND("'",A1)-1))+IF(ISERROR(FIND("""",A1)),0,--(IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"* */*")=0),"0 ","")&SUBSTITUTE(MID(A1,IF(ISERROR(FIND("-",A1)),1,FIND("-",A1)+1),255),"""","")))/12 When I use that formula for those values I get: 1/2"____ 0.0416666666666667 3/8"____ 0.03125 11/16"__ 0.0572916666666667 What am I missing? *********** Regards, Ron XL2002, WinXP-Pro "Biff" wrote: Pretty close, Ron. It crashes on entries like: 1/2" 3/8" 11/16" It evaluates as the date serial number divided by 12: 1/2 = 38719/12 = 3226.583 I thought the OP wanted to convert to decimal inches. At least that's what the example in the post indicates. Biff "Ron Coderre" wrote in message ... It's not pretty, but near as I can tell, this formula handles all of your posted examples: For a value in A1 B1: =--IF(ISERROR(FIND("'",A1)),0,IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"*'")0),"0 ","")&LEFT(A1,FIND("'",A1)-1))+IF(ISERROR(FIND("""",A1)),0,--(IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"* */*")=0),"0 ","")&SUBSTITUTE(MID(A1,IF(ISERROR(FIND("-",A1)),1,FIND("-",A1)+1),255),"""","")))/12 That works as long as "they" don't use fractions greater than 1 for FEET (eg 1 1/2') Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "dingy101" wrote: Hi, 1'-7 3/8" 1'-10" 1' - would be 1'-0", not 12" 2 1/2" 8" 1/2" - rare but possible, not in this set , but in future 10' -0 15/32" - also possible, 2 digits in feet and fraction in 32 nds These items have been manually entered into a worksheet, came from Autocad drawings. I have about six sheets with about 500 entries. I need to total items to get a figure for amount of steel to purchase. I appreciate your efforts, I am suprised there is not a "set" way to do this. Thanks, Gary "Biff" wrote: Ok, just to give you an idea of how "brutal" this is: Just for this single format: 1'-7 3/8" Requires this formula: (it could be slightly reduced as I wrote it with the intention of including all the possible formats) =IF(ISNUMBER(FIND("'",A10)),LEFT(A10,FIND("'",A10)-1)*12,0)+IF(COUNT(FIND({"-"," "},A10))=2,MID(A10,FIND("-",A10)+1,FIND(" ",A10)-1-FIND("-",A10)),0)+IF(COUNT(FIND({" ","/"},A10))=2,MID(A10,FIND(" ",A10)+1,FIND("/",A10)-1-FIND(" ",A10))/MID(A10,FIND("/",A10)+1,FIND("""",A10)-1-FIND("/",A10)),0) A single formula that accounts for all the possible formats would be a real nightmare! Which leads me to ask about ALL THE POSSIBLE formats. You listed these: 2 1/2" 8" 10 1/2" 1'-7 3/8" But I came up these: 1'-7 3/8" 1'-10" 1' 2 1/2" 8" 1/2" How about if the measurement is one foot. Will that be entered as 12" or 1' (as I have listed above) ? Where do these values come from? Are they manually entered by a user? Are they imported from some other app? Copy/pasted from a web site? If you could enter each unit of measure into separate cells this would be a piece of cake! If they are from some other app and you can't parse them out, then..... I would recommend a separate formula breaking each measure into a separate cell then summing those cells together. For example, one cell to calculate the feet, one cell for the whole inches and one cell for the fractional inches. The formulas for the inches would still be kind of long and ugly! There's just too many different formats to contend with! Before I continue with this let me know what you think and let me know ALL the possible formats. It can be done but it ain't easy. Biff "dingy101" wrote in message ... No, 2 1/2" 8" 10 1/2" 1'-7 3/8" These are the different configurations. Gary "Biff" wrote: Is the format ALWAYS the same? In other words, will there ALWAYS be some feet, some inches and some fraction of an inch? Biff "dingy101" wrote in message ... Is there a way to convert a value in a cell that is 2'-3 1/2" to a decimal such as 27.5" ? excel 2003 SP2 Thanks Gary |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert feet/inches to decimal
No change!
I thought that was a typo at first but before I edited it out I did try it as both: (COUNTIF(A1,"**/*")=0) (COUNTIF(A1,"* */*")=0) Neither made a difference so that's when I edited it out. Kind of strange why you would get: 1/2"____ 0.0416666666666667 3/8"____ 0.03125 11/16"__ 0.0572916666666667 Wanna swap files? xlcanhelpatcomcastperiodnet Remove the can and change the obvious. Biff "Ron Coderre" wrote in message ... There is a small typo (I think!) in the formula but it's superflous: (COUNTIF(A1,"**/*")=0) That gets line wrapped (in OE) right between the 2 asterisks. I just edited one of them out. Nope! That's not a typo in my formula, Biff. It's just an unfortunate place for the window to wrap. That part of the formula should be: (COUNTIF(A1,"* */*")=0) Notice the criteria is "asterisk_space_asterisk_slash_asterisk". That's the part of the formula that tests if fractions have a space in front of them to prevent the kind of unwanted date conversion you described. If no, the next part of the formula puts a zero and a space in front of the fraction. Try that and see if it works properly for you. *********** Best regards, Ron XL2002, WinXP-Pro "Biff" wrote: Hmmm...... That's strange! Here's a screencap: http://img151.imageshack.us/img151/6421/feetinch4bz.jpg Your formula is in column B. I put another formula in column C to convert to decimal inches. There is a small typo (I think!) in the formula but it's superflous: (COUNTIF(A1,"**/*")=0) That gets line wrapped (in OE) right between the 2 asterisks. I just edited one of them out. ??? Biff "Ron Coderre" wrote in message ... Hey, Biff...I'm a bit puzzled. =--IF(ISERROR(FIND("'",A1)),0,IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"*'")0),"0 ","")&LEFT(A1,FIND("'",A1)-1))+IF(ISERROR(FIND("""",A1)),0,--(IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"* */*")=0),"0 ","")&SUBSTITUTE(MID(A1,IF(ISERROR(FIND("-",A1)),1,FIND("-",A1)+1),255),"""","")))/12 When I use that formula for those values I get: 1/2"____ 0.0416666666666667 3/8"____ 0.03125 11/16"__ 0.0572916666666667 What am I missing? *********** Regards, Ron XL2002, WinXP-Pro "Biff" wrote: Pretty close, Ron. It crashes on entries like: 1/2" 3/8" 11/16" It evaluates as the date serial number divided by 12: 1/2 = 38719/12 = 3226.583 I thought the OP wanted to convert to decimal inches. At least that's what the example in the post indicates. Biff "Ron Coderre" wrote in message ... It's not pretty, but near as I can tell, this formula handles all of your posted examples: For a value in A1 B1: =--IF(ISERROR(FIND("'",A1)),0,IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"*'")0),"0 ","")&LEFT(A1,FIND("'",A1)-1))+IF(ISERROR(FIND("""",A1)),0,--(IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"* */*")=0),"0 ","")&SUBSTITUTE(MID(A1,IF(ISERROR(FIND("-",A1)),1,FIND("-",A1)+1),255),"""","")))/12 That works as long as "they" don't use fractions greater than 1 for FEET (eg 1 1/2') Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "dingy101" wrote: Hi, 1'-7 3/8" 1'-10" 1' - would be 1'-0", not 12" 2 1/2" 8" 1/2" - rare but possible, not in this set , but in future 10' -0 15/32" - also possible, 2 digits in feet and fraction in 32 nds These items have been manually entered into a worksheet, came from Autocad drawings. I have about six sheets with about 500 entries. I need to total items to get a figure for amount of steel to purchase. I appreciate your efforts, I am suprised there is not a "set" way to do this. Thanks, Gary "Biff" wrote: Ok, just to give you an idea of how "brutal" this is: Just for this single format: 1'-7 3/8" Requires this formula: (it could be slightly reduced as I wrote it with the intention of including all the possible formats) =IF(ISNUMBER(FIND("'",A10)),LEFT(A10,FIND("'",A10)-1)*12,0)+IF(COUNT(FIND({"-"," "},A10))=2,MID(A10,FIND("-",A10)+1,FIND(" ",A10)-1-FIND("-",A10)),0)+IF(COUNT(FIND({" ","/"},A10))=2,MID(A10,FIND(" ",A10)+1,FIND("/",A10)-1-FIND(" ",A10))/MID(A10,FIND("/",A10)+1,FIND("""",A10)-1-FIND("/",A10)),0) A single formula that accounts for all the possible formats would be a real nightmare! Which leads me to ask about ALL THE POSSIBLE formats. You listed these: 2 1/2" 8" 10 1/2" 1'-7 3/8" But I came up these: 1'-7 3/8" 1'-10" 1' 2 1/2" 8" 1/2" How about if the measurement is one foot. Will that be entered as 12" or 1' (as I have listed above) ? Where do these values come from? Are they manually entered by a user? Are they imported from some other app? Copy/pasted from a web site? If you could enter each unit of measure into separate cells this would be a piece of cake! If they are from some other app and you can't parse them out, then..... I would recommend a separate formula breaking each measure into a separate cell then summing those cells together. For example, one cell to calculate the feet, one cell for the whole inches and one cell for the fractional inches. The formulas for the inches would still be kind of long and ugly! There's just too many different formats to contend with! Before I continue with this let me know what you think and let me know ALL the possible formats. It can be done but it ain't easy. Biff "dingy101" wrote in message ... No, 2 1/2" 8" 10 1/2" 1'-7 3/8" These are the different configurations. Gary "Biff" wrote: Is the format ALWAYS the same? In other words, will there ALWAYS be some feet, some inches and some fraction of an inch? Biff "dingy101" wrote in message ... Is there a way to convert a value in a cell that is 2'-3 1/2" to a decimal such as 27.5" ? excel 2003 SP2 Thanks Gary |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert feet/inches to decimal
Wanna swap files?<
Good idea!. Mine is on its way. Please send yours. ronSKIPTHIScoderre AT bigfoot DOT com *********** Best regards, Ron XL2002, WinXP-Pro "Biff" wrote: No change! I thought that was a typo at first but before I edited it out I did try it as both: (COUNTIF(A1,"**/*")=0) (COUNTIF(A1,"* */*")=0) Neither made a difference so that's when I edited it out. Kind of strange why you would get: 1/2"____ 0.0416666666666667 3/8"____ 0.03125 11/16"__ 0.0572916666666667 Wanna swap files? xlcanhelpatcomcastperiodnet Remove the can and change the obvious. Biff "Ron Coderre" wrote in message ... There is a small typo (I think!) in the formula but it's superflous: (COUNTIF(A1,"**/*")=0) That gets line wrapped (in OE) right between the 2 asterisks. I just edited one of them out. Nope! That's not a typo in my formula, Biff. It's just an unfortunate place for the window to wrap. That part of the formula should be: (COUNTIF(A1,"* */*")=0) Notice the criteria is "asterisk_space_asterisk_slash_asterisk". That's the part of the formula that tests if fractions have a space in front of them to prevent the kind of unwanted date conversion you described. If no, the next part of the formula puts a zero and a space in front of the fraction. Try that and see if it works properly for you. *********** Best regards, Ron XL2002, WinXP-Pro "Biff" wrote: Hmmm...... That's strange! Here's a screencap: http://img151.imageshack.us/img151/6421/feetinch4bz.jpg Your formula is in column B. I put another formula in column C to convert to decimal inches. There is a small typo (I think!) in the formula but it's superflous: (COUNTIF(A1,"**/*")=0) That gets line wrapped (in OE) right between the 2 asterisks. I just edited one of them out. ??? Biff "Ron Coderre" wrote in message ... Hey, Biff...I'm a bit puzzled. =--IF(ISERROR(FIND("'",A1)),0,IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"*'")0),"0 ","")&LEFT(A1,FIND("'",A1)-1))+IF(ISERROR(FIND("""",A1)),0,--(IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"* */*")=0),"0 ","")&SUBSTITUTE(MID(A1,IF(ISERROR(FIND("-",A1)),1,FIND("-",A1)+1),255),"""","")))/12 When I use that formula for those values I get: 1/2"____ 0.0416666666666667 3/8"____ 0.03125 11/16"__ 0.0572916666666667 What am I missing? *********** Regards, Ron XL2002, WinXP-Pro "Biff" wrote: Pretty close, Ron. It crashes on entries like: 1/2" 3/8" 11/16" It evaluates as the date serial number divided by 12: 1/2 = 38719/12 = 3226.583 I thought the OP wanted to convert to decimal inches. At least that's what the example in the post indicates. Biff "Ron Coderre" wrote in message ... It's not pretty, but near as I can tell, this formula handles all of your posted examples: For a value in A1 B1: =--IF(ISERROR(FIND("'",A1)),0,IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"*'")0),"0 ","")&LEFT(A1,FIND("'",A1)-1))+IF(ISERROR(FIND("""",A1)),0,--(IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"* */*")=0),"0 ","")&SUBSTITUTE(MID(A1,IF(ISERROR(FIND("-",A1)),1,FIND("-",A1)+1),255),"""","")))/12 That works as long as "they" don't use fractions greater than 1 for FEET (eg 1 1/2') Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "dingy101" wrote: Hi, 1'-7 3/8" 1'-10" 1' - would be 1'-0", not 12" 2 1/2" 8" 1/2" - rare but possible, not in this set , but in future 10' -0 15/32" - also possible, 2 digits in feet and fraction in 32 nds These items have been manually entered into a worksheet, came from Autocad drawings. I have about six sheets with about 500 entries. I need to total items to get a figure for amount of steel to purchase. I appreciate your efforts, I am suprised there is not a "set" way to do this. Thanks, Gary "Biff" wrote: Ok, just to give you an idea of how "brutal" this is: Just for this single format: 1'-7 3/8" Requires this formula: (it could be slightly reduced as I wrote it with the intention of including all the possible formats) =IF(ISNUMBER(FIND("'",A10)),LEFT(A10,FIND("'",A10)-1)*12,0)+IF(COUNT(FIND({"-"," "},A10))=2,MID(A10,FIND("-",A10)+1,FIND(" ",A10)-1-FIND("-",A10)),0)+IF(COUNT(FIND({" ","/"},A10))=2,MID(A10,FIND(" ",A10)+1,FIND("/",A10)-1-FIND(" ",A10))/MID(A10,FIND("/",A10)+1,FIND("""",A10)-1-FIND("/",A10)),0) A single formula that accounts for all the possible formats would be a real nightmare! Which leads me to ask about ALL THE POSSIBLE formats. You listed these: 2 1/2" 8" 10 1/2" 1'-7 3/8" But I came up these: 1'-7 3/8" 1'-10" 1' 2 1/2" 8" 1/2" How about if the measurement is one foot. Will that be entered as 12" or 1' (as I have listed above) ? Where do these values come from? Are they manually entered by a user? Are they imported from some other app? Copy/pasted from a web site? If you could enter each unit of measure into separate cells this would be a piece of cake! If they are from some other app and you can't parse them out, then..... I would recommend a separate formula breaking each measure into a separate cell then summing those cells together. For example, one cell to calculate the feet, one cell for the whole inches and one cell for the fractional inches. The formulas for the inches would still be kind of long and ugly! There's just too many different formats to contend with! Before I continue with this let me know what you think and let me know ALL the possible formats. It can be done but it ain't easy. Biff "dingy101" wrote in message ... No, 2 1/2" 8" 10 1/2" 1'-7 3/8" These are the different configurations. Gary "Biff" wrote: Is the format ALWAYS the same? In other words, will there ALWAYS be some feet, some inches and some fraction of an inch? Biff "dingy101" wrote in message ... Is there a way to convert a value in a cell that is 2'-3 1/2" to a decimal such as 27.5" ? excel 2003 SP2 Thanks Gary |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert feet/inches to decimal
OK!
We got this all straightened out!!!!! Seems I was the victim of more than one instance of line wrap. Ron's formula does work with a minor tweak: =(--IF(ISERROR(FIND("'",A1)),0,IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"*'")0),"0","") &LEFT(A1,FIND("'",A1)-1))+IF(ISERROR(FIND("""",A1)),0,--(IF(COUNTIF(A1,"*/*") *(COUNTIF(A1,"* */*")=0),"0 ","")&SUBSTITUTE(MID(A1,IF(ISERROR(FIND("-",A1)) ,1,FIND("-",A1)+1),255),"""","")))/12)*12 I tried "cutting" that into chunks so the line wraps won't disguise the needed spaces. That works on the following formats: 1'-10 13/16" 1'-0 5/32" 1'-10" 1'-0" 2 1/2" 10" 1/2" Biff "Ron Coderre" wrote in message ... Wanna swap files?< Good idea!. Mine is on its way. Please send yours. ronSKIPTHIScoderre AT bigfoot DOT com *********** Best regards, Ron XL2002, WinXP-Pro "Biff" wrote: No change! I thought that was a typo at first but before I edited it out I did try it as both: (COUNTIF(A1,"**/*")=0) (COUNTIF(A1,"* */*")=0) Neither made a difference so that's when I edited it out. Kind of strange why you would get: 1/2"____ 0.0416666666666667 3/8"____ 0.03125 11/16"__ 0.0572916666666667 Wanna swap files? xlcanhelpatcomcastperiodnet Remove the can and change the obvious. Biff "Ron Coderre" wrote in message ... There is a small typo (I think!) in the formula but it's superflous: (COUNTIF(A1,"**/*")=0) That gets line wrapped (in OE) right between the 2 asterisks. I just edited one of them out. Nope! That's not a typo in my formula, Biff. It's just an unfortunate place for the window to wrap. That part of the formula should be: (COUNTIF(A1,"* */*")=0) Notice the criteria is "asterisk_space_asterisk_slash_asterisk". That's the part of the formula that tests if fractions have a space in front of them to prevent the kind of unwanted date conversion you described. If no, the next part of the formula puts a zero and a space in front of the fraction. Try that and see if it works properly for you. *********** Best regards, Ron XL2002, WinXP-Pro "Biff" wrote: Hmmm...... That's strange! Here's a screencap: http://img151.imageshack.us/img151/6421/feetinch4bz.jpg Your formula is in column B. I put another formula in column C to convert to decimal inches. There is a small typo (I think!) in the formula but it's superflous: (COUNTIF(A1,"**/*")=0) That gets line wrapped (in OE) right between the 2 asterisks. I just edited one of them out. ??? Biff "Ron Coderre" wrote in message ... Hey, Biff...I'm a bit puzzled. =--IF(ISERROR(FIND("'",A1)),0,IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"*'")0),"0 ","")&LEFT(A1,FIND("'",A1)-1))+IF(ISERROR(FIND("""",A1)),0,--(IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"* */*")=0),"0 ","")&SUBSTITUTE(MID(A1,IF(ISERROR(FIND("-",A1)),1,FIND("-",A1)+1),255),"""","")))/12 When I use that formula for those values I get: 1/2"____ 0.0416666666666667 3/8"____ 0.03125 11/16"__ 0.0572916666666667 What am I missing? *********** Regards, Ron XL2002, WinXP-Pro "Biff" wrote: Pretty close, Ron. It crashes on entries like: 1/2" 3/8" 11/16" It evaluates as the date serial number divided by 12: 1/2 = 38719/12 = 3226.583 I thought the OP wanted to convert to decimal inches. At least that's what the example in the post indicates. Biff "Ron Coderre" wrote in message ... It's not pretty, but near as I can tell, this formula handles all of your posted examples: For a value in A1 B1: =--IF(ISERROR(FIND("'",A1)),0,IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"*'")0),"0 ","")&LEFT(A1,FIND("'",A1)-1))+IF(ISERROR(FIND("""",A1)),0,--(IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"* */*")=0),"0 ","")&SUBSTITUTE(MID(A1,IF(ISERROR(FIND("-",A1)),1,FIND("-",A1)+1),255),"""","")))/12 That works as long as "they" don't use fractions greater than 1 for FEET (eg 1 1/2') Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "dingy101" wrote: Hi, 1'-7 3/8" 1'-10" 1' - would be 1'-0", not 12" 2 1/2" 8" 1/2" - rare but possible, not in this set , but in future 10' -0 15/32" - also possible, 2 digits in feet and fraction in 32 nds These items have been manually entered into a worksheet, came from Autocad drawings. I have about six sheets with about 500 entries. I need to total items to get a figure for amount of steel to purchase. I appreciate your efforts, I am suprised there is not a "set" way to do this. Thanks, Gary "Biff" wrote: Ok, just to give you an idea of how "brutal" this is: Just for this single format: 1'-7 3/8" Requires this formula: (it could be slightly reduced as I wrote it with the intention of including all the possible formats) =IF(ISNUMBER(FIND("'",A10)),LEFT(A10,FIND("'",A10)-1)*12,0)+IF(COUNT(FIND({"-"," "},A10))=2,MID(A10,FIND("-",A10)+1,FIND(" ",A10)-1-FIND("-",A10)),0)+IF(COUNT(FIND({" ","/"},A10))=2,MID(A10,FIND(" ",A10)+1,FIND("/",A10)-1-FIND(" ",A10))/MID(A10,FIND("/",A10)+1,FIND("""",A10)-1-FIND("/",A10)),0) A single formula that accounts for all the possible formats would be a real nightmare! Which leads me to ask about ALL THE POSSIBLE formats. You listed these: 2 1/2" 8" 10 1/2" 1'-7 3/8" But I came up these: 1'-7 3/8" 1'-10" 1' 2 1/2" 8" 1/2" How about if the measurement is one foot. Will that be entered as 12" or 1' (as I have listed above) ? Where do these values come from? Are they manually entered by a user? Are they imported from some other app? Copy/pasted from a web site? If you could enter each unit of measure into separate cells this would be a piece of cake! If they are from some other app and you can't parse them out, then..... I would recommend a separate formula breaking each measure into a separate cell then summing those cells together. For example, one cell to calculate the feet, one cell for the whole inches and one cell for the fractional inches. The formulas for the inches would still be kind of long and ugly! There's just too many different formats to contend with! Before I continue with this let me know what you think and let me know ALL the possible formats. It can be done but it ain't easy. Biff "dingy101" wrote in message ... No, 2 1/2" 8" 10 1/2" 1'-7 3/8" These are the different configurations. Gary "Biff" wrote: Is the format ALWAYS the same? In other words, will there ALWAYS be some feet, some inches and some fraction of an inch? Biff "dingy101" wrote in message ... Is there a way to convert a value in a cell that is 2'-3 1/2" to a decimal such as 27.5" ? excel 2003 SP2 Thanks Gary |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert feet/inches to decimal
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" wrote in message ... Wanna swap files?< Good idea!. Mine is on its way. Please send yours. ronSKIPTHIScoderre AT bigfoot DOT com *********** Best regards, Ron XL2002, WinXP-Pro "Biff" wrote: No change! I thought that was a typo at first but before I edited it out I did try it as both: (COUNTIF(A1,"**/*")=0) (COUNTIF(A1,"* */*")=0) Neither made a difference so that's when I edited it out. Kind of strange why you would get: 1/2"____ 0.0416666666666667 3/8"____ 0.03125 11/16"__ 0.0572916666666667 Wanna swap files? xlcanhelpatcomcastperiodnet Remove the can and change the obvious. Biff "Ron Coderre" wrote in message ... There is a small typo (I think!) in the formula but it's superflous: (COUNTIF(A1,"**/*")=0) That gets line wrapped (in OE) right between the 2 asterisks. I just edited one of them out. Nope! That's not a typo in my formula, Biff. It's just an unfortunate place for the window to wrap. That part of the formula should be: (COUNTIF(A1,"* */*")=0) Notice the criteria is "asterisk_space_asterisk_slash_asterisk". That's the part of the formula that tests if fractions have a space in front of them to prevent the kind of unwanted date conversion you described. If no, the next part of the formula puts a zero and a space in front of the fraction. Try that and see if it works properly for you. *********** Best regards, Ron XL2002, WinXP-Pro "Biff" wrote: Hmmm...... That's strange! Here's a screencap: http://img151.imageshack.us/img151/6421/feetinch4bz.jpg Your formula is in column B. I put another formula in column C to convert to decimal inches. There is a small typo (I think!) in the formula but it's superflous: (COUNTIF(A1,"**/*")=0) That gets line wrapped (in OE) right between the 2 asterisks. I just edited one of them out. ??? Biff "Ron Coderre" wrote in message ... Hey, Biff...I'm a bit puzzled. =--IF(ISERROR(FIND("'",A1)),0,IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"*'")0),"0 ","")&LEFT(A1,FIND("'",A1)-1))+IF(ISERROR(FIND("""",A1)),0,--(IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"* */*")=0),"0 ","")&SUBSTITUTE(MID(A1,IF(ISERROR(FIND("-",A1)),1,FIND("-",A1)+1),255),"""","")))/12 When I use that formula for those values I get: 1/2"____ 0.0416666666666667 3/8"____ 0.03125 11/16"__ 0.0572916666666667 What am I missing? *********** Regards, Ron XL2002, WinXP-Pro "Biff" wrote: Pretty close, Ron. It crashes on entries like: 1/2" 3/8" 11/16" It evaluates as the date serial number divided by 12: 1/2 = 38719/12 = 3226.583 I thought the OP wanted to convert to decimal inches. At least that's what the example in the post indicates. Biff "Ron Coderre" wrote in message ... It's not pretty, but near as I can tell, this formula handles all of your posted examples: For a value in A1 B1: =--IF(ISERROR(FIND("'",A1)),0,IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"*'")0),"0 ","")&LEFT(A1,FIND("'",A1)-1))+IF(ISERROR(FIND("""",A1)),0,--(IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"* */*")=0),"0 ","")&SUBSTITUTE(MID(A1,IF(ISERROR(FIND("-",A1)),1,FIND("-",A1)+1),255),"""","")))/12 That works as long as "they" don't use fractions greater than 1 for FEET (eg 1 1/2') Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "dingy101" wrote: Hi, 1'-7 3/8" 1'-10" 1' - would be 1'-0", not 12" 2 1/2" 8" 1/2" - rare but possible, not in this set , but in future 10' -0 15/32" - also possible, 2 digits in feet and fraction in 32 nds These items have been manually entered into a worksheet, came from Autocad drawings. I have about six sheets with about 500 entries. I need to total items to get a figure for amount of steel to purchase. I appreciate your efforts, I am suprised there is not a "set" way to do this. Thanks, Gary "Biff" wrote: Ok, just to give you an idea of how "brutal" this is: Just for this single format: 1'-7 3/8" Requires this formula: (it could be slightly reduced as I wrote it with the intention of including all the possible formats) =IF(ISNUMBER(FIND("'",A10)),LEFT(A10,FIND("'",A10)-1)*12,0)+IF(COUNT(FIND({"-"," "},A10))=2,MID(A10,FIND("-",A10)+1,FIND(" ",A10)-1-FIND("-",A10)),0)+IF(COUNT(FIND({" ","/"},A10))=2,MID(A10,FIND(" ",A10)+1,FIND("/",A10)-1-FIND(" ",A10))/MID(A10,FIND("/",A10)+1,FIND("""",A10)-1-FIND("/",A10)),0) A single formula that accounts for all the possible formats would be a real nightmare! Which leads me to ask about ALL THE POSSIBLE formats. You listed these: 2 1/2" 8" 10 1/2" 1'-7 3/8" But I came up these: 1'-7 3/8" 1'-10" 1' 2 1/2" 8" 1/2" How about if the measurement is one foot. Will that be entered as 12" or 1' (as I have listed above) ? Where do these values come from? Are they manually entered by a user? Are they imported from some other app? Copy/pasted from a web site? If you could enter each unit of measure into separate cells this would be a piece of cake! |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert feet/inches to decimal
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" wrote in message ... 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" wrote in message ... Wanna swap files?< Good idea!. Mine is on its way. Please send yours. ronSKIPTHIScoderre AT bigfoot DOT com *********** Best regards, Ron XL2002, WinXP-Pro "Biff" wrote: No change! I thought that was a typo at first but before I edited it out I did try it as both: (COUNTIF(A1,"**/*")=0) (COUNTIF(A1,"* */*")=0) Neither made a difference so that's when I edited it out. Kind of strange why you would get: 1/2"____ 0.0416666666666667 3/8"____ 0.03125 11/16"__ 0.0572916666666667 Wanna swap files? xlcanhelpatcomcastperiodnet Remove the can and change the obvious. Biff "Ron Coderre" wrote in message ... There is a small typo (I think!) in the formula but it's superflous: (COUNTIF(A1,"**/*")=0) That gets line wrapped (in OE) right between the 2 asterisks. I just edited one of them out. Nope! That's not a typo in my formula, Biff. It's just an unfortunate place for the window to wrap. That part of the formula should be: (COUNTIF(A1,"* */*")=0) Notice the criteria is "asterisk_space_asterisk_slash_asterisk". That's the part of the formula that tests if fractions have a space in front of them to prevent the kind of unwanted date conversion you described. If no, the next part of the formula puts a zero and a space in front of the fraction. Try that and see if it works properly for you. *********** Best regards, Ron XL2002, WinXP-Pro "Biff" wrote: Hmmm...... That's strange! Here's a screencap: http://img151.imageshack.us/img151/6421/feetinch4bz.jpg Your formula is in column B. I put another formula in column C to convert to decimal inches. There is a small typo (I think!) in the formula but it's superflous: (COUNTIF(A1,"**/*")=0) That gets line wrapped (in OE) right between the 2 asterisks. I just edited one of them out. ??? Biff "Ron Coderre" wrote in message ... Hey, Biff...I'm a bit puzzled. =--IF(ISERROR(FIND("'",A1)),0,IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"*'")0),"0 ","")&LEFT(A1,FIND("'",A1)-1))+IF(ISERROR(FIND("""",A1)),0,--(IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"* */*")=0),"0 ","")&SUBSTITUTE(MID(A1,IF(ISERROR(FIND("-",A1)),1,FIND("-",A1)+1),255),"""","")))/12 When I use that formula for those values I get: 1/2"____ 0.0416666666666667 3/8"____ 0.03125 11/16"__ 0.0572916666666667 What am I missing? *********** Regards, Ron XL2002, WinXP-Pro "Biff" wrote: Pretty close, Ron. It crashes on entries like: 1/2" 3/8" 11/16" It evaluates as the date serial number divided by 12: 1/2 = 38719/12 = 3226.583 I thought the OP wanted to convert to decimal inches. At least that's what the example in the post indicates. Biff "Ron Coderre" wrote in message ... It's not pretty, but near as I can tell, this formula handles all of your posted examples: For a value in A1 B1: =--IF(ISERROR(FIND("'",A1)),0,IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"*'")0),"0 ","")&LEFT(A1,FIND("'",A1)-1))+IF(ISERROR(FIND("""",A1)),0,--(IF(COUNTIF(A1,"*/*")*(COUNTIF(A1,"* */*")=0),"0 ","")&SUBSTITUTE(MID(A1,IF(ISERROR(FIND("-",A1)),1,FIND("-",A1)+1),255),"""","")))/12 That works as long as "they" don't use fractions greater than 1 for FEET (eg 1 1/2') Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "dingy101" wrote: Hi, 1'-7 3/8" 1'-10" 1' - would be 1'-0", not 12" 2 1/2" 8" 1/2" - rare but possible, not in this set , but in future 10' -0 15/32" - also possible, 2 digits in feet and fraction in 32 nds These items have been manually entered into a worksheet, came from Autocad drawings. I have about six sheets with about 500 entries. I need to total items to get a figure for amount of steel to purchase. I appreciate your efforts, I am suprised there is not a "set" way to do this. Thanks, Gary "Biff" wrote: Ok, just to give you an idea of how "brutal" this is: Just for this single format: 1'-7 3/8" Requires this formula: (it could be slightly reduced as I wrote it with the intention of including all the possible formats) =IF(ISNUMBER(FIND("'",A10)),LEFT(A10,FIND("'",A10)-1)*12,0)+IF(COUNT(FIND({"-"," "},A10))=2,MID(A10,FIND("-",A10)+1,FIND(" ",A10)-1-FIND("-",A10)),0)+IF(COUNT(FIND({" ","/"},A10))=2,MID(A10,FIND(" ",A10)+1,FIND("/",A10)-1-FIND(" ",A10))/MID(A10,FIND("/",A10)+1,FIND("""",A10)-1-FIND("/",A10)),0) A single formula that accounts for all the possible formats would be a real nightmare! Which leads me to ask about ALL THE POSSIBLE formats. You listed these: 2 1/2" 8" 10 1/2" 1'-7 3/8" But I came up these: 1'-7 3/8" 1'-10" 1' 2 1/2" 8" 1/2" How about if the measurement is one foot. Will that be entered as 12" or 1' (as I have listed above) ? Where do these values come from? Are they manually entered by a user? Are they imported from some other app? Copy/pasted from a web site? If you could enter each unit of measure into separate cells this would be a piece of cake! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert imported data with . separator for decimal into , separato | Excel Discussion (Misc queries) | |||
Convert decimal degree (lattitude/longitude) into Degree, | Excel Discussion (Misc queries) | |||
Convert decimal hour into time format? | Excel Discussion (Misc queries) | |||
convert decimal number to time : convert 1,59 (minutes, dec) to m | Excel Discussion (Misc queries) | |||
how to convert total hours (HH:MM) into decimal number | Excel Worksheet Functions |