Decimals to fractions, multiple dimensions in one field

[seanne]

I have a field that contains height, length, width in this format:
7.874" x 7.874" x 19.685",

I need to convert each of these dimensions to a fraction to the closest quarter fraction... 1/4, 1/2, 3/4
so my end result should look like this:
7 3/4" x 7 3/4" x 19 3/4",

I have used the format cell to fraction function, but in order to use that function the numbers need to be stripped into their own separate field AND the inch sign and X need to be removed or it will not work.
Then the new number as a fraction would need to be concatenated back together again and add the " and X back in.

I need a macro to do this.
OR
is there an easier way?
maybe an If/Then scenario?

please help I'd appreciate any ideas.

[FDibbins]

Hi and welcome to the forum

I would suggest that you break this down into 4 helpers - 3 to run the calcs, 1 to combine them. You CAN do it all in 1 formula, but if you need to edit it, it will be a nightmare.

for the 1st part...
=LEFT(A1,FIND(".",A1,1)-1)&" "&IF(MID(A1,FIND(".",A1,1)+1,3)/1000-MOD(MID(A1,FIND(".",A1,1)+1,3)/1000,0.25)=0.5,"1/2",(MID(A1,FIND(".",A1,1)+1,3)/1000-MOD(MID(A1,FIND(".",A1,1)+1,3)/1000,0.25))/0.25&"/4")

for the middle..
=MID(A1,FIND("x ",A1,1)+2,1)&" "&IF(MID(A1,FIND("x ",A1,1)+4,3)/1000-MOD(MID(A1,FIND("x ",A1,1)+4,3)/1000,0.25)=0.5,"1/2",(MID(A1,FIND("x ",A1,1)+4,3)/1000-MOD(MID(A1,FIND("x ",A1,1)+4,3)/1000,0.25))/0.25&"/4")

for the last part...
=MID(A1,FIND("x ",A1,10)+2,2)*1&" "&IF(MID(A1,FIND("x ",A1,10)+5,3)/1000-MOD(MID(A1,FIND("x ",A1,10)+5,3)/1000,0.25)=0.5,"1/2",(MID(A1,FIND("x ",A1,10)+5,3)/1000-MOD(MID(A1,FIND("x ",A1,10)+5,3)/1000,0.25))/0.25&"/4")

Then just combine them together...
=B8&"'' x "&E8&"'' x " &G8&" ''"

As you can see, it's quite possible to combine these, but................

[Leith Ross]

Hello seanne,

This is a User Defined Function that will convert the field into 1/4 inch increments. Add a VBA module to to your workbook's VBA Project and paste the code into it. You can then call the UDF like any other Excel function.

Example

• Cell A1 value 7.874" x 7.874" x 19.685"
• Cell B1 Formula =QtrFrac(A1)
• Cell B1 value 7 3/4" x 7 3/4" x 19 3/4"

UDF Macro Code

Please Login or Register  to view this content.

[seanne]

Thank you for your calcs. I am not a macro whiz so I was unable to run your suggestion. I did find another way, however.
I used pivet table to break apart my dimensions into new separate fields and convert them to fractions.

However I am having a hard time combining the dimensions back together again.
When I use the concatonate function to bring them back together the Fractions turn back into decimals again.
I have tried to turn the fractions to text but that also turns them back to decimals.

Any suggestions?

[Leith Ross]

Hello seanne,

To Install the UDF:

1. Copy the UDF code using Ctrl+C
2. Open your workbook and press the keys Alt+F11
3. Press the keys Alt+I then press the key M
4. Paste the macro by pressing the Ctrl+V keys.
5. Save the macro by pressing the keys Ctrl+S
6. Exit the VBA by pressing the keys Alt+Q

Now, call the UDF just like you would when you add a formula.

Please Login or Register  to view this content.

[seanne]

Thanks so much for your help. The macro you provided DOES work for the exact set of dimensions I provided.
However it doesn't work for most of the dimensions on my spread sheet however.
part of the problem I am having is that dimensions are not always exactly the same number of characters per line.
and if even ONE extra character is thrown in, for example if the second dimension is 17.874 instead of 7.874 the formula doesn't work.

see example
Your macros work perfect IF there are 3 decimal places AND the exact number of characters shown in the first line.
However I am dealing with a variety of numbers and that doesn't work.

These work:
7.874" x 7.874" x 19.685", 7 3/4 7 3/4 19 1/2 7 3/4'' x 7 3/4'' x 19 1/2 ''
4.843" x 0.394" x 12.756", 4 3/4 0 1/4 12 3/4 4 3/4'' x 0 1/4'' x 12 3/4 ''
4.528" x 2.489" x 14.921", 4 1/2 2 1/4 14 3/4 4 1/2'' x 2 1/4'' x 14 3/4 ''

But these don't:
87.008" x 0.752" x 0.752", 87 0/4 0 3/4 #VALUE! #VALUE!
8.07" x 3.74" x 7.87", #VALUE! #VALUE! #VALUE! #VALUE!
57.087" x 4.724" x 1", 57 0/4 4 1/2 #VALUE! #VALUE!
5.512" x 1.575" x 1.575", 5 1/2 1 1/2 #VALUE! #VALUE!

any way the macro can be written to encompass more values? and NOT use counting characters?

SeAnne

[seanne]

I am attaching an excel file that has the formula inserted to show what does and does not work.
thanks