CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsformulafractions
Mary
Mary

Reputation: 63

Convert number to fraction using symbols like ¼ ½ ¾ (in google spreadsheets)

I would like to convert a number to a fraction in Google spreadsheets (I'm on the latest Firefox, Windows 7). After some searching I managed to find this formula which works well for me (It converts cell A1 into a fraction):

=IF(INT(A1)=A1,A1,IF(INT(A1)>0 ; INT(A1) &" " ; "")&(INDEX(SPLIT((TEXT( MOD(A1;1);"000000000000000E000")); "E");1;1))/GCD((INDEX(SPLIT((TEXT( MOD(A1;1); "000000000000000E000"));"E");1;1));(10^(-1*INDEX(SPLIT((TEXT( MOD(A1;1);"000000000000000E000")); "E");1;2))))&"/"& (10^(-1*INDEX(SPLIT((TEXT( MOD(A1;1);"000000000000000E000")); "E");1;2)))/GCD((INDEX(SPLIT((TEXT( MOD(A1;1); "000000000000000E000"));"E");1;1));(10^(-1*INDEX(SPLIT((TEXT( MOD(A1;1);"000000000000000E000")); "E");1;2)))))

What I would like to do is use symbols like ¼ ½ and ¾ (so that for example the formula converts 1.5 to "1 ½"). These are symbols I copied and pasted from Microsoft Word. They paste into Google spreadsheets fine but as text, so I'm guessing I need to add Concatenate into this formula, but I don't know how.

My value in cell A1 will always be a multiple of 0.25 (e.g. 0.5 or 2 or 3.75 etc), so I will only need the symbols ¼ ½ and ¾.

If anyone know how do this preferably with a formula or otherwise a script I would be very grateful indeed.

Upvotes: 1

Views: 6383

Answers (2)

Mogsdad
Mogsdad

Reputation: 45710

Various decimal-to-fraction converters have been implemented in Javascript, for example this one.

That function could be imported and used as a custom spreadsheet function. It is a simple extension to replace fractions with the equivalent ASCII or Unicode characters.

A custom function will be far more readable than implementing a complex spreadsheet function.

=dec2frac(A1)

Upvotes: 0

William Seiti Mizuta
William Seiti Mizuta

Reputation: 7985

Considering that you only have multiples of 0.25, the following formula serves to you. If you have a value that it is not multiple of 0.25, it will fails.

=IF(INT(A1)=A1,A1,IF(INT(A1)>0 ; INT(A1) &" " ; "")&(if(A1-INT(A1)>0.5; "¾"; if(A1-INT(A1)>0.25; "½"; "¼"))))

Upvotes: 1

Related Questions