Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
Converting from feet, inches and fractions to inches and decimal p
Morning,
We have a program that exports a table from Auto-cad to Excel, using a program called TableBuilder. It exports using feet, inches and fractions eg 6'-5 1/2" or 4'-0". I'm looking for a way to convert this to inches and decimal points and all the -, ', " removed. for example 6'-5 1/2" will be 77.5 and 4'-0" will be 48 Any ideas? It's a huge spreadsheet and doing each conversion individually will drive me nuts. Cheers in advance Dee |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
Converting from feet, inches and fractions to inches and decimal p
It will be a complex formula (almost finished), but to avoid problems
with converting cell references can you tell me which is the first cell that this will apply to in your sheet, eg F2, and which column you want the formula to go into? Then I can give you the exact formula which you will be able to copy/paste into your sheet. Pete On Sep 17, 10:06 pm, Dee wrote: Morning, We have a program that exports a table from Auto-cad to Excel, using a program called TableBuilder. It exports using feet, inches and fractions eg 6'-5 1/2" or 4'-0". I'm looking for a way to convert this to inches and decimal points and all the -, ', " removed. for example 6'-5 1/2" will be 77.5 and 4'-0" will be 48 Any ideas? It's a huge spreadsheet and doing each conversion individually will drive me nuts. Cheers in advance Dee |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
Converting from feet, inches and fractions to inches and decim
That would be amazing! The first cell this needs to applied to is D5 and the
same column D. Thank you in advance Dee "Pete_UK" wrote: It will be a complex formula (almost finished), but to avoid problems with converting cell references can you tell me which is the first cell that this will apply to in your sheet, eg F2, and which column you want the formula to go into? Then I can give you the exact formula which you will be able to copy/paste into your sheet. Pete On Sep 17, 10:06 pm, Dee wrote: Morning, We have a program that exports a table from Auto-cad to Excel, using a program called TableBuilder. It exports using feet, inches and fractions eg 6'-5 1/2" or 4'-0". I'm looking for a way to convert this to inches and decimal points and all the -, ', " removed. for example 6'-5 1/2" will be 77.5 and 4'-0" will be 48 Any ideas? It's a huge spreadsheet and doing each conversion individually will drive me nuts. Cheers in advance Dee |
#4
Posted to microsoft.public.excel.setup
|
|||
|
|||
Converting from feet, inches and fractions to inches and decimal p
Hi Dee,
I can see on Google Groups that you have replied, but I can't read your reply,and I couldn't find it at all on the microsoft discussions site - any chance you can post it again to see if I can read that one? Pete On Sep 18, 10:35 am, Pete_UK wrote: It will be a complex formula (almost finished), but to avoid problems with converting cell references can you tell me which is the first cell that this will apply to in your sheet, eg F2, and which column you want the formula to go into? Then I can give you the exact formula which you will be able to copy/paste into your sheet. Pete On Sep 17, 10:06 pm, Dee wrote: Morning, We have a program that exports a table from Auto-cad to Excel, using a program called TableBuilder. It exports using feet, inches and fractions eg 6'-5 1/2" or 4'-0". I'm looking for a way to convert this to inches and decimal points and all the -, ', " removed. for example 6'-5 1/2" will be 77.5 and 4'-0" will be 48 Any ideas? It's a huge spreadsheet and doing each conversion individually will drive me nuts. Cheers in advance Dee- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.setup
|
|||
|
|||
Converting from feet, inches and fractions to inches and decimal p
It's alright - I can see now that your first cell is D5. You can't put
the formula in the same column as it will overwrite the data that you have, so make use of an empty column (eg column H) and put this formula in H5: =IF(ISNUMBER(FIND(" ",D5)),LEFT(D5,FIND("'",D5)-1)*12+MID(D5,FIND("-",D5)+1,2)+MID(D5,FIND(" ",D5),FIND("/",D5)-FIND(" ",D5))/MID(SUBSTITUTE(D5,CHAR(34)," "),FIND("/",D5)+1,3),LEFT(D5,FIND("'",D5)-1)*12+MID(SUBSTITUTE(D5,CHAR(34)," "),FIND("-",D5)+1,2)) This is all one formula, so be wary of spurious line-breaks that the newsgroups sometimes introduce (usually showing up as hyphens). I've tested it out on your examples and also on 14'-11 23/64", which returns the correct result of 179.359375, so it seems to work. Format the cell with the appropriate number of decimal places, and then copy the formula down. If you don't want this extra column in your sheet, you can fix the values from the formula and then paste them over the original values in column D and then delete column H. Hope this helps. Pete On Sep 18, 2:01 pm, Pete_UK wrote: Hi Dee, I can see on Google Groups that you have replied, but I can't read your reply,and I couldn't find it at all on the microsoft discussions site - any chance you can post it again to see if I can read that one? Pete On Sep 18, 10:35 am, Pete_UK wrote: It will be a complex formula (almost finished), but to avoid problems with converting cell references can you tell me which is the first cell that this will apply to in your sheet, eg F2, and which column you want the formula to go into? Then I can give you the exact formula which you will be able to copy/paste into your sheet. Pete On Sep 17, 10:06 pm, Dee wrote: Morning, We have a program that exports a table from Auto-cad to Excel, using a program called TableBuilder. It exports using feet, inches and fractions eg 6'-5 1/2" or 4'-0". I'm looking for a way to convert this to inches and decimal points and all the -, ', " removed. for example 6'-5 1/2" will be 77.5 and 4'-0" will be 48 Any ideas? It's a huge spreadsheet and doing each conversion individually will drive me nuts. Cheers in advance Dee- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.setup
|
|||
|
|||
Converting from feet, inches and fractions to inches and decim
Hey Pete,
Thank you again. I'll give it a go later today, when I'm in work. Regs Dee "Pete_UK" wrote: It's alright - I can see now that your first cell is D5. You can't put the formula in the same column as it will overwrite the data that you have, so make use of an empty column (eg column H) and put this formula in H5: =IF(ISNUMBER(FIND(" ",D5)),LEFT(D5,FIND("'",D5)-1)*12+MID(D5,FIND("-",D5)+1,2)+MID(D5,FIND(" ",D5),FIND("/",D5)-FIND(" ",D5))/MID(SUBSTITUTE(D5,CHAR(34)," "),FIND("/",D5)+1,3),LEFT(D5,FIND("'",D5)-1)*12+MID(SUBSTITUTE(D5,CHAR(34)," "),FIND("-",D5)+1,2)) This is all one formula, so be wary of spurious line-breaks that the newsgroups sometimes introduce (usually showing up as hyphens). I've tested it out on your examples and also on 14'-11 23/64", which returns the correct result of 179.359375, so it seems to work. Format the cell with the appropriate number of decimal places, and then copy the formula down. If you don't want this extra column in your sheet, you can fix the values from the formula and then paste them over the original values in column D and then delete column H. Hope this helps. Pete On Sep 18, 2:01 pm, Pete_UK wrote: Hi Dee, I can see on Google Groups that you have replied, but I can't read your reply,and I couldn't find it at all on the microsoft discussions site - any chance you can post it again to see if I can read that one? Pete On Sep 18, 10:35 am, Pete_UK wrote: It will be a complex formula (almost finished), but to avoid problems with converting cell references can you tell me which is the first cell that this will apply to in your sheet, eg F2, and which column you want the formula to go into? Then I can give you the exact formula which you will be able to copy/paste into your sheet. Pete On Sep 17, 10:06 pm, Dee wrote: Morning, We have a program that exports a table from Auto-cad to Excel, using a program called TableBuilder. It exports using feet, inches and fractions eg 6'-5 1/2" or 4'-0". I'm looking for a way to convert this to inches and decimal points and all the -, ', " removed. for example 6'-5 1/2" will be 77.5 and 4'-0" will be 48 Any ideas? It's a huge spreadsheet and doing each conversion individually will drive me nuts. Cheers in advance Dee- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert decimal to feet and inches | Excel Worksheet Functions | |||
Fractions, Feet, Inches, & macros | Excel Discussion (Misc queries) | |||
Convert feet/inches to decimal | Excel Worksheet Functions | |||
How do i convert feet and inches to a decimal value? | Excel Worksheet Functions | |||
Converting inches to feet & inches. | Excel Worksheet Functions |