Convert feet and inches to inches

[DennisG]

Hi All

Need to convert the following : 2.0 to 24 & 3.0 to 36 This is the way I receive it and need to convert it as indicated.
I try =(LEFT(B2,1)*12)+(RIGHT(B2,1)) but I get 26 and 39

Please advise

Thanks Dennis

 

[NARAYANK991]

Hi ,

When you give examples , please give examples which show the variation in the input data as much as possible.

2 ft. is represented as 2.0 , while 3 ft. is represented as 3.0 ; this does not tell us how 2 ft. and 4 inches will be represented or how 2 ft. and 11 inches will be represented.

Narayan

 

[DennisG]

This is the way is listed and written in the page when ordering a product:
Example:
1- 4.2 x 1.2 x.0.8 which equals 1pc. 4'2"L x 1'2"W x.08" H
so I need to convert it to say in a single cell 50, and in separate cell 14

Hope this is clear

Thanks

 

[SirJB7]

Hi, DennisG!

Assuming your 3-dimension (W-L-H) size in Feet.Inch is in A2, like " a.b x c.d x.e.f " (unquoted), where digits of a, c, e (and b, d, f too) can be of any value, try this:

Width:
=VALOR(IZQUIERDA(ESPACIOS(IZQUIERDA(A2;HALLAR("x";A2)-1));HALLAR(".";ESPACIOS(IZQUIERDA(A2;HALLAR("x";A2)-1)))-1))*12+VALOR(DERECHA(ESPACIOS(IZQUIERDA(A2;HALLAR("x";A2)-1));LARGO(ESPACIOS(IZQUIERDA(A2;HALLAR("x";A2)-1)))-HALLAR(".";ESPACIOS(IZQUIERDA(A2;HALLAR("x";A2)-1))))) -----> in English:
=VALUE(LEFT(TRIM(LEFT(A2,SEARCH("x",A2)-1)),SEARCH(".",TRIM(LEFT(A2,SEARCH("x",A2)-1)))-1))*12+VALUE(RIGHT(TRIM(LEFT(A2,SEARCH("x",A2)-1)),LEN(TRIM(LEFT(A2,SEARCH("x",A2)-1)))-SEARCH(".",TRIM(LEFT(A2,SEARCH("x",A2)-1)))))

Length:
=VALOR(IZQUIERDA(ESPACIOS(EXTRAE(A2;HALLAR("x";A2)+1;HALLAR("x";A2;HALLAR("x";A2)-1)));HALLAR(".";ESPACIOS(EXTRAE(A2;HALLAR("x";A2)+1;HALLAR("x";A2;HALLAR("x";A2)-1))))-1))*12+VALOR(DERECHA(ESPACIOS(EXTRAE(A2;HALLAR("x";A2)+1;HALLAR("x";A2;HALLAR("x";A2)-1)));LARGO(ESPACIOS(EXTRAE(A2;HALLAR("x";A2)+1;HALLAR("x";A2;HALLAR("x";A2)-1))))-HALLAR(".";ESPACIOS(EXTRAE(A2;HALLAR("x";A2)+1;HALLAR("x";A2;HALLAR("x";A2)-1)))))) -----> in English:
=VALUE(LEFT(TRIM(MID(A2,SEARCH("x",A2)+1,SEARCH("x",A2,SEARCH("x",A2)-1))),SEARCH(".",TRIM(MID(A2,SEARCH("x",A2)+1,SEARCH("x",A2,SEARCH("x",A2)-1))))-1))*12+VALUE(RIGHT(TRIM(MID(A2,SEARCH("x",A2)+1,SEARCH("x",A2,SEARCH("x",A2)-1))),LEN(TRIM(MID(A2,SEARCH("x",A2)+1,SEARCH("x",A2,SEARCH("x",A2)-1))))-SEARCH(".",TRIM(MID(A2,SEARCH("x",A2)+1,SEARCH("x",A2,SEARCH("x",A2)-1))))))

Height:
=VALOR(IZQUIERDA(ESPACIOS(DERECHA(A2;LARGO(A2)-HALLAR("x";A2;HALLAR("x";A2)+1)-1));HALLAR(".";ESPACIOS(DERECHA(A2;LARGO(A2)-HALLAR("x";A2;HALLAR("x";A2)+1)-1)))-1))*12+VALOR(DERECHA(ESPACIOS(DERECHA(A2;LARGO(A2)-HALLAR("x";A2;HALLAR("x";A2)+1)-1));LARGO(ESPACIOS(DERECHA(A2;LARGO(A2)-HALLAR("x";A2;HALLAR("x";A2)+1)-1)))-HALLAR(".";ESPACIOS(DERECHA(A2;LARGO(A2)-HALLAR("x";A2;HALLAR("x";A2)+1)-1))))) -----> in English:
=VALUE(LEFT(TRIM(RIGHT(A2,LEN(A2)-SEARCH("x",A2,SEARCH("x",A2)+1)-1)),SEARCH(".",TRIM(RIGHT(A2,LEN(A2)-SEARCH("x",A2,SEARCH("x",A2)+1)-1)))-1))*12+VALUE(RIGHT(TRIM(RIGHT(A2,LEN(A2)-SEARCH("x",A2,SEARCH("x",A2)+1)-1)),LEN(TRIM(RIGHT(A2,LEN(A2)-SEARCH("x",A2,SEARCH("x",A2)+1)-1)))-SEARCH(".",TRIM(RIGHT(A2,LEN(A2)-SEARCH("x",A2,SEARCH("x",A2)+1)-1)))))

Said so, direct formulas might be a bit complex. In the attached file you may find a 2-step way to work with easier formulas using a helper column for each measure.

Regards!

 

[bosco_yip]

Another option



In B2, formula copy across and down :

=IFERROR(INT(TRIM(MID(SUBSTITUTE(SUBSTITUTE(" x "&$A2&".",".",":")," x ",REPT(" ",50)),COLUMN(A2)*50,50))*24)*12+MINUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(" x "&TEXT($A2,"#.00"),".",":")," x ",REPT(" ",50)),COLUMN(A2)*50,50))),"")

Regards
Bosco

 

[bosco_yip]

In B2, formula copy across and down :
=IFERROR(INT(TRIM(MID(SUBSTITUTE(SUBSTITUTE(" x "&$A2&".",".",":")," x ",REPT(" ",50)),COLUMN(A2)*50,50))*24)*12+MINUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(" x "&TEXT($A2,"#.00"),".",":")," x ",REPT(" ",50)),COLUMN(A2)*50,50))),"")
Bosco

The B2 formula can be further reduced to :

=IFERROR(INT(TRIM(MID(SUBSTITUTE("x"&$A2,"x",REPT(" ",50)),COLUMN(A2)*50,50)))*12+MINUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE("x"&TEXT($A2,"#.00"),".",":"),"x",REPT(" ",50)),COLUMN(A2)*50,50))),"")

Regards
Bosco