Formula help

[Carl Brehm]

In column b is a size list in the following format

500X360X750

The sizes are in mm. need a formula in column h that would change this to
inches in fractions i.e..
14 3/4 X 9 X 16 3/4

There can be 3, 4 or 5 numbers in each set

I can use =(TEXT(CONVERT((LEFT(B3,(SEARCH("x",B3)-1))),"mm","in"),"# ?/4"))

Along with mid and right to pull each set into a different cell then bring
them back together in one cell as text.
There must be an easier way.
Any help would be appreciated

--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN Lories, Quakers
Mitred Conures, TAG's, Bourkes
Cages

 

[David McRitchie]

As worksheet formulas you would have to split them up as you indicate.
Your ?/4 will not reduce to lowest common denominator. You might
want to take a look at formulas by Bernie Deitrick on my site..
Fractions rounded to 1/4, 1/8, 1/16, 1/32, 1/64, 1/128
http://www.mvps.org/dmcritchie/excel/fractex1.htm

 

[Carl Brehm]

The ?/4 rounds to the 1/4 which is what I want .
Would it be easier to do as a macro? Using 4 columns for formulas for this
is a little much and would greatly enlarge the spreadsheet as their is 5
worksheets full to do.

Thanks

--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN Lories, Quakers
Mitred Conures, TAG's, Bourkes
Cages

 

[David McRitchie]

2/4 is what you get for .5 which may or may not make a difference
to you. If it does use the reference to Bernie's formulas.

You can concatenate the formulas instead of the cells when you
are satisfied with the formulas in each of the three columns..