Formula on cell with text and numbers

[marsipulami]

Hey,

I need to convert an overview of dimensions from imperial to metric.
the original sizes were entered within 1 cell. Example 27.5"W x 32"D x 31"H.

is it possible to run a formula on that 1 cell (I need to multiply the numbers by 2.54) so I don't need to do all conversions manually?

thanks!

[Glenn Kennedy]

This might not cover every circumstance, depending on just how representative your one example was. However, It's a start. You can have the answers in separate cells, or in a single cell. see the sheet attached.

[AlKey]

First paste this formula in C1 and pull it to E1 and then down.

=ROUND(2.54*TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,CHAR(34)," "),"x"," "),"W"," "),"H"," "),"D"," "))," ",REPT(" ",255)),(COLUMNS($A:A)-1)*255+1,255))+0,2)

Now paste this formula in B1 and copy it down

=C1&CHAR(34)&"W x "&D1&CHAR(34)&"D x "&E1&CHAR(34)&"H"

Row\Col	A	B	C	D	E
1	27.5"W x 32"D x 31"H	69.85"W x 81.28"D x 78.74"H	69.85	81.28	78.74
2	45"W x 28"D x 30"H	114.3"W x 71.12"D x 76.2"H	114.30	71.12	76.20
3	27.8"W x 53"D x 17"H	70.61"W x 134.62"D x 43.18"H	70.61	134.62	43.18
4	25.9"W x 49"D x 18"H	65.79"W x 124.46"D x 45.72"H	65.79	124.46	45.72
5	19.7"W x 13.5"D x 31.0"H	50.04"W x 34.29"D x 78.74"H	50.04	34.29	78.74