[FIXED] How to convert a decimal to a fraction than convert the result to a text?

Issue

In this case I have a series of dimensions that need to be converted for upload into another database.

21.3 x 16.9 x 14.6

Needs to become

21 5/16 x 16 14/16 x 14 10/16

I can split up the numbers and reformat that into the correct fractions, but this is only for display in that cell. If you click on the cell itself, you can see in the task bar that they remain decimals.

How do I make the change permanent?

Solution

Basicall you will need to save them as text. Use the TEXT formula:

TEXT(A1,"# ??/16")

Which will change the decimal in A1 to a fraction.

To do the whole in one formula TEXTJOIN() as an Array Formula:

=TEXTJOIN(" x ",TRUE,TEXT(--TRIM(MID(SUBSTITUTE(A1,"x",REPT(" ",99)),(ROW($1:$3)-1)*99+1,99)),"# ??/16"))

Being an Array Formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

TEXTJOIN was introduced with Office 365 Excel.

Answered By - Scott Craner

Answer Checked By - David Marino (PHPFixing Volunteer)