| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change feet and inches to decimal inches
I have feet and inches in a cell as text and want it to be inches as a decimal.
Before (2"-4 1/2") after (28.5) I would like to us a formula or number type to convert. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
change feet and inches to decimal inches
I put the formula in and it seems to subtract the inches and fraction from
the feet. 4'-7 1/4 comes out as 40.75. ???? close but no cigar. "JoeU2004" wrote: "Brian 1" <Brian wrote: I have feet and inches in a cell as text and want it to be inches as a decimal. Before (2"-4 1/2") after (28.5) Does the following suit your needs (assumes the text form is in A1): =12*LEFT(A1, FIND(CHAR(39),A1)-1) +TEXT(MID(A1, 1+FIND(CHAR(39), A1), LEN(A1)-FIND(CHAR(39),A1)-1), "General") You can use General or any numeric format for the cell. Instead of CHAR(39), you could write "'", which is an apostrophe between double-quotes. (Hard to read!) Note: That assumes you meant to write 2' 4 1/2", not literally 2"-4 1/2". If you truly meant the latter (surprise!), the following should do the trick: =12*LEFT(A1, FIND(CHAR(34),A1)-1) +TEXT(MID(A1, 1+FIND("-",A1), LEN(A1)-FIND("-",A1)-1), "General") where CHAR(34) is the ASCII code for double-quote. ----- original message ----- "Brian 1" <Brian wrote in message ... I have feet and inches in a cell as text and want it to be inches as a decimal. Before (2"-4 1/2") after (28.5) I would like to us a formula or number type to convert. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
change feet and inches to decimal inches
Try this one:
=12*LEFT(A1, FIND(CHAR(39),A1)-1)+TEXT(MID(A1, 1+FIND("-", A1), LEN(A1)-FIND("-",A1)),"General") Had to change the Char(39)'s in Find to "-" Regards, Fred. "Brian 1" wrote in message ... I put the formula in and it seems to subtract the inches and fraction from the feet. 4'-7 1/4 comes out as 40.75. ???? close but no cigar. "JoeU2004" wrote: "Brian 1" <Brian wrote: I have feet and inches in a cell as text and want it to be inches as a decimal. Before (2"-4 1/2") after (28.5) Does the following suit your needs (assumes the text form is in A1): =12*LEFT(A1, FIND(CHAR(39),A1)-1) +TEXT(MID(A1, 1+FIND(CHAR(39), A1), LEN(A1)-FIND(CHAR(39),A1)-1), "General") You can use General or any numeric format for the cell. Instead of CHAR(39), you could write "'", which is an apostrophe between double-quotes. (Hard to read!) Note: That assumes you meant to write 2' 4 1/2", not literally 2"-4 1/2". If you truly meant the latter (surprise!), the following should do the trick: =12*LEFT(A1, FIND(CHAR(34),A1)-1) +TEXT(MID(A1, 1+FIND("-",A1), LEN(A1)-FIND("-",A1)-1), "General") where CHAR(34) is the ASCII code for double-quote. ----- original message ----- "Brian 1" <Brian wrote in message ... I have feet and inches in a cell as text and want it to be inches as a decimal. Before (2"-4 1/2") after (28.5) I would like to us a formula or number type to convert. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
change feet and inches to decimal inches
"Brian 1" wrote:
I put the formula in and it seems to subtract the inches and fraction from the feet. 4'-7 1/4 comes out as 40.75. ???? close but no cigar. Well, you have now presented two different forms for your text. Close, but no cigar. I already gave you a second formula to deal with the "-". But you cannot seem decide if feet is indicated by single or double-quote, and if inches is followed by double-quote. The mistake is yours, not mine. ----- original message ----- "Brian 1" wrote in message ... I put the formula in and it seems to subtract the inches and fraction from the feet. 4'-7 1/4 comes out as 40.75. ???? close but no cigar. "JoeU2004" wrote: "Brian 1" <Brian wrote: I have feet and inches in a cell as text and want it to be inches as a decimal. Before (2"-4 1/2") after (28.5) Does the following suit your needs (assumes the text form is in A1): =12*LEFT(A1, FIND(CHAR(39),A1)-1) +TEXT(MID(A1, 1+FIND(CHAR(39), A1), LEN(A1)-FIND(CHAR(39),A1)-1), "General") You can use General or any numeric format for the cell. Instead of CHAR(39), you could write "'", which is an apostrophe between double-quotes. (Hard to read!) Note: That assumes you meant to write 2' 4 1/2", not literally 2"-4 1/2". If you truly meant the latter (surprise!), the following should do the trick: =12*LEFT(A1, FIND(CHAR(34),A1)-1) +TEXT(MID(A1, 1+FIND("-",A1), LEN(A1)-FIND("-",A1)-1), "General") where CHAR(34) is the ASCII code for double-quote. ----- original message ----- "Brian 1" <Brian wrote in message ... I have feet and inches in a cell as text and want it to be inches as a decimal. Before (2"-4 1/2") after (28.5) I would like to us a formula or number type to convert. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| convert decimal inches into feet and inches | Excel Discussion (Misc queries) | |||
| change decimal to feet and inches | Excel Discussion (Misc queries) | |||
| Convert inches & display as Feet Inches and Fractions -- BUG FREE | Excel Worksheet Functions | |||
| How do I convert decimal to feet and inches | Excel Worksheet Functions | |||
| Converting from feet, inches and fractions to inches and decimal p | Setting up and Configuration of Excel | |||
Hybrid Mode
