change feet and inches to decimal inches

  • Thread starter Thread starter Brian 1
  • Start date Start date
B

Brian 1

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.
 
Brian 1 said:
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)
Click to expand...

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 -----
 
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.
 
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 said:
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.
Click to expand...

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 -----
 
Back
Top