Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to create formula to convert?
I have a column of measurments ie. 33' 8 1/2" which I need to convert to
numeric value to compute square footage. all I get as result is error. A1 = width, A2: A40 = unique measurments.... D2:D40 = length measurments.... I need to convert (ft) & (in) to numeric value multiply A$ by D$ = X and convert? I think excell can do this but I'm to green to figure out? Any help is greatly appreciated. Thank you.. |
#2
|
|||
|
|||
One way:
If all your values have both feet and inches (e.g., 8 feet is 8' 0"): =LEFT(A2,FIND("'",A2)-1)+MID(LEFT(A2,LEN(A2)-1), FIND("'",A2)+1,255)/12 will convert the measurement to decimal. In article , "ynot" wrote: I have a column of measurments ie. 33' 8 1/2" which I need to convert to numeric value to compute square footage. all I get as result is error. A1 = width, A2: A40 = unique measurments.... D2:D40 = length measurments.... I need to convert (ft) & (in) to numeric value multiply A$ by D$ = X and convert? I think excell can do this but I'm to green to figure out? Any help is greatly appreciated. Thank you.. |
#3
|
|||
|
|||
thank you for your prompt response as I should have noted I'm a true rookie to excel, as I only had the 2.5 minute training and probaly should have bosted to the dummies for excel..... all values in column "A" = X' Y"; if I try to paste formule to B2.... which it won't allow I see MID(text, start_num,NUM_CHARS) which the paste place =LEFT(A2,FIND("'",A2)-1)+MID(LEFT(A2,LEN(A2)-1), in A2 and FIND("'",A2)+1,255)/12 in A3..... did I choke something? AGAIN thank you for your supportand patience..... "JE McGimpsey" wrote: One way: If all your values have both feet and inches (e.g., 8 feet is 8' 0"): =LEFT(A2,FIND("'",A2)-1)+MID(LEFT(A2,LEN(A2)-1), FIND("'",A2)+1,255)/12 will convert the measurement to decimal. In article , "ynot" wrote: I have a column of measurments ie. 33' 8 1/2" which I need to convert to numeric value to compute square footage. all I get as result is error. A1 = width, A2: A40 = unique measurments.... D2:D40 = length measurments.... I need to convert (ft) & (in) to numeric value multiply A$ by D$ = X and convert? I think excell can do this but I'm to green to figure out? Any help is greatly appreciated. Thank you.. |
#4
|
|||
|
|||
It's all one formula and to be entered as a single line in a single cell.
Either type it in manually or try copying and then pasting into a plain text editor like Notepad. Place the cursor at the beginning of each line and backspace so there are no spaces. Kind of hard to explain! Biff "ynot" wrote in message ... thank you for your prompt response as I should have noted I'm a true rookie to excel, as I only had the 2.5 minute training and probaly should have bosted to the dummies for excel..... all values in column "A" = X' Y"; if I try to paste formule to B2.... which it won't allow I see MID(text, start_num,NUM_CHARS) which the paste place =LEFT(A2,FIND("'",A2)-1)+MID(LEFT(A2,LEN(A2)-1), in A2 and FIND("'",A2)+1,255)/12 in A3..... did I choke something? AGAIN thank you for your supportand patience..... "JE McGimpsey" wrote: One way: If all your values have both feet and inches (e.g., 8 feet is 8' 0"): =LEFT(A2,FIND("'",A2)-1)+MID(LEFT(A2,LEN(A2)-1), FIND("'",A2)+1,255)/12 will convert the measurement to decimal. In article , "ynot" wrote: I have a column of measurments ie. 33' 8 1/2" which I need to convert to numeric value to compute square footage. all I get as result is error. A1 = width, A2: A40 = unique measurments.... D2:D40 = length measurments.... I need to convert (ft) & (in) to numeric value multiply A$ by D$ = X and convert? I think excell can do this but I'm to green to figure out? Any help is greatly appreciated. Thank you.. |
#5
|
|||
|
|||
Sounds like XL interpreted the formula as tabular for some reason.
Select B2, click in the Edit Formula box on the Formula Bar and paste. Remove any spurious line feeds. In article , "ynot" wrote: thank you for your prompt response as I should have noted I'm a true rookie to excel, as I only had the 2.5 minute training and probaly should have bosted to the dummies for excel..... all values in column "A" = X' Y"; if I try to paste formule to B2.... which it won't allow I see MID(text, start_num,NUM_CHARS) which the paste place =LEFT(A2,FIND("'",A2)-1)+MID(LEFT(A2,LEN(A2)-1), in A2 and FIND("'",A2)+1,255)/12 in A3..... did I choke something? |
#6
|
|||
|
|||
Thank you for your patience and continued help!! still can't get it to work?
obviously I'm in over my head..yet I can cut, paste, type, but I don't know why I get a message that there is an error in the formula... this is the exact formula I've pasted : =LEFT(A2,FIND("'",A2)-1)+MID(LEFT(A2,LEN(A2)-1),FIND("'",A2)+1,255)/12 however I get that damn error message? then I go to INSERT_FUNCTION which brings up an argument widow with three boxes .... the 1st box Find_text has "'",A2)-1)+MID(LEFT(A2,LEN(A2)-1),FIND("'" = Invalid 2nd box Within_text has A2 = 33' 0" and the third Start_num = blank = number in white scale? Any suggestions? Again, thank you for your kind and appreciative knowledge!!!! you are a true scholar! "JE McGimpsey" wrote: Sounds like XL interpreted the formula as tabular for some reason. Select B2, click in the Edit Formula box on the Formula Bar and paste. Remove any spurious line feeds. In article , "ynot" wrote: thank you for your prompt response as I should have noted I'm a true rookie to excel, as I only had the 2.5 minute training and probaly should have bosted to the dummies for excel..... all values in column "A" = X' Y"; if I try to paste formule to B2.... which it won't allow I see MID(text, start_num,NUM_CHARS) which the paste place =LEFT(A2,FIND("'",A2)-1)+MID(LEFT(A2,LEN(A2)-1), in A2 and FIND("'",A2)+1,255)/12 in A3..... did I choke something? |
#7
|
|||
|
|||
thanks biff, I tried it both ways and got the same result. I recognize there
is a strong possibility of user error and being a newby to excel w/o knowing the root of formula creation creates additional issues. appricate your thoughts. "Biff" wrote: It's all one formula and to be entered as a single line in a single cell. Either type it in manually or try copying and then pasting into a plain text editor like Notepad. Place the cursor at the beginning of each line and backspace so there are no spaces. Kind of hard to explain! Biff "ynot" wrote in message ... thank you for your prompt response as I should have noted I'm a true rookie to excel, as I only had the 2.5 minute training and probaly should have bosted to the dummies for excel..... all values in column "A" = X' Y"; if I try to paste formule to B2.... which it won't allow I see MID(text, start_num,NUM_CHARS) which the paste place =LEFT(A2,FIND("'",A2)-1)+MID(LEFT(A2,LEN(A2)-1), in A2 and FIND("'",A2)+1,255)/12 in A3..... did I choke something? AGAIN thank you for your supportand patience..... "JE McGimpsey" wrote: One way: If all your values have both feet and inches (e.g., 8 feet is 8' 0"): =LEFT(A2,FIND("'",A2)-1)+MID(LEFT(A2,LEN(A2)-1), FIND("'",A2)+1,255)/12 will convert the measurement to decimal. In article , "ynot" wrote: I have a column of measurments ie. 33' 8 1/2" which I need to convert to numeric value to compute square footage. all I get as result is error. A1 = width, A2: A40 = unique measurments.... D2:D40 = length measurments.... I need to convert (ft) & (in) to numeric value multiply A$ by D$ = X and convert? I think excell can do this but I'm to green to figure out? Any help is greatly appreciated. Thank you.. |
#8
|
|||
|
|||
The formula you show below that you got from JE works just fine in my XL2k.
What version of Excel are you using?, and is it the English language version?..........sometimes there are variations in some formulas based on what language it is..........try pasting it into a completely new worksheet to prove it to yourself that it works......if it doesn't work there, then there is probably something wrong in Excel.........if it does, then it's something in that your original file. Vaya con Dios, Chuck, CABGx3 "ynot" wrote in message ... Thank you for your patience and continued help!! still can't get it to work? obviously I'm in over my head..yet I can cut, paste, type, but I don't know why I get a message that there is an error in the formula... this is the exact formula I've pasted : =LEFT(A2,FIND("'",A2)-1)+MID(LEFT(A2,LEN(A2)-1),FIND("'",A2)+1,255)/12 however I get that damn error message? then I go to INSERT_FUNCTION which brings up an argument widow with three boxes .... the 1st box Find_text has "'",A2)-1)+MID(LEFT(A2,LEN(A2)-1),FIND("'" = Invalid 2nd box Within_text has A2 = 33' 0" and the third Start_num = blank = number in white scale? Any suggestions? Again, thank you for your kind and appreciative knowledge!!!! you are a true scholar! "JE McGimpsey" wrote: Sounds like XL interpreted the formula as tabular for some reason. Select B2, click in the Edit Formula box on the Formula Bar and paste. Remove any spurious line feeds. In article , "ynot" wrote: thank you for your prompt response as I should have noted I'm a true rookie to excel, as I only had the 2.5 minute training and probaly should have bosted to the dummies for excel..... all values in column "A" = X' Y"; if I try to paste formule to B2.... which it won't allow I see MID(text, start_num,NUM_CHARS) which the paste place =LEFT(A2,FIND("'",A2)-1)+MID(LEFT(A2,LEN(A2)-1), in A2 and FIND("'",A2)+1,255)/12 in A3..... did I choke something? |
#9
|
|||
|
|||
xl 2003..... its still not working even when I open new .xls, enter in A2 33'
0" and paste in B2 =LEFT(A2,FIND("'",A2)-1)+MID(LEFT(A2,LEN(A2)-1),FIND("'",A2)+1,255)/12 which I can't resolve? "CLR" wrote: The formula you show below that you got from JE works just fine in my XL2k. What version of Excel are you using?, and is it the English language version?..........sometimes there are variations in some formulas based on what language it is..........try pasting it into a completely new worksheet to prove it to yourself that it works......if it doesn't work there, then there is probably something wrong in Excel.........if it does, then it's something in that your original file. Vaya con Dios, Chuck, CABGx3 "ynot" wrote in message ... Thank you for your patience and continued help!! still can't get it to work? obviously I'm in over my head..yet I can cut, paste, type, but I don't know why I get a message that there is an error in the formula... this is the exact formula I've pasted : =LEFT(A2,FIND("'",A2)-1)+MID(LEFT(A2,LEN(A2)-1),FIND("'",A2)+1,255)/12 however I get that damn error message? then I go to INSERT_FUNCTION which brings up an argument widow with three boxes .... the 1st box Find_text has "'",A2)-1)+MID(LEFT(A2,LEN(A2)-1),FIND("'" = Invalid 2nd box Within_text has A2 = 33' 0" and the third Start_num = blank = number in white scale? Any suggestions? Again, thank you for your kind and appreciative knowledge!!!! you are a true scholar! "JE McGimpsey" wrote: Sounds like XL interpreted the formula as tabular for some reason. Select B2, click in the Edit Formula box on the Formula Bar and paste. Remove any spurious line feeds. In article , "ynot" wrote: thank you for your prompt response as I should have noted I'm a true rookie to excel, as I only had the 2.5 minute training and probaly should have bosted to the dummies for excel..... all values in column "A" = X' Y"; if I try to paste formule to B2.... which it won't allow I see MID(text, start_num,NUM_CHARS) which the paste place =LEFT(A2,FIND("'",A2)-1)+MID(LEFT(A2,LEN(A2)-1), in A2 and FIND("'",A2)+1,255)/12 in A3..... did I choke something? |
#10
|
|||
|
|||
What does "not working" mean? Do you get a specific error message?
Are you in a country which uses "," for a decimal separator (unusual if you're using ' and ", but...)? If so, you'll need to substitute ";" for ",": =LEFT(A2;FIND("'";A2)-1)+MID(LEFT(A2;LEN(A2)-1);FIND("'";A2)+1;255)/12 What happens when you break it up: =LEFT(A2,FIND("'",A2)-1) =MID(LEFT(A2,LEN(A2)-1),FIND("'",A2)+1,255)/12 In article , "ynot" wrote: xl 2003..... its still not working even when I open new .xls, enter in A2 33' 0" and paste in B2 =LEFT(A2,FIND("'",A2)-1)+MID(LEFT(A2,LEN(A2)-1),FIND("'",A2)+1,255)/12 which I can't resolve? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
How to create specific formula | Excel Worksheet Functions | |||
In two workbooks, how do I create a formula that will look at a o. | Excel Discussion (Misc queries) | |||
Create formula | Excel Worksheet Functions | |||
How do I create a formula in Excel that will countif or sumif bef. | Excel Worksheet Functions |