Google Sheets: How to format decimal as fraction in drop down list (data validation)

Question

I have a cell with data validation. The source of the validation is a long range of decimals ~~in 0.0078125 (1/128) increments.~~

The cell containing the validation is formatted to display fractions # ???/???

The validation drop down displays decimals 0.000000

~~How do I make the drop down list format the decimals as fractions?~~

How do I format the drop down list to display fractions?

Edit to add clarification images.

Formatted cell displaying fractions:

Formatted cell and menu not displaying fractions when drop down menu activated:

I would like to have these displayed as fractions.

Tedinoz · Accepted Answer · 2021-07-13 06:41:05Z

0

To display fractions in the dropdown, you need to convert the decimal/formatted value to a text value.

For example:

=text(A1:A5,"# ???/???")

If the resulting dropdown value is to be used in a calculation, then you need to convert it back to a number.

For example:

=left(B10,search("/",B10)-1)/right(B10,len(B10)-search("/",B10))

SAMPLE

answered Jul 13, 2021 at 6:41

Tedinoz

5,8412 gold badges13 silver badges31 bronze badges

That does the trick, thank you!
– Anthony
Commented Jul 13, 2021 at 16:56
I noticed mixed fractions tend to have additional spaces in their formatting: #[space][space]???/???[space] This becomes evident when passing the value to a function and converting to decimal. I stripped out the additional spaces using regex, but do you know why they appear and how to prevent it?
– Anthony
Commented Jul 14, 2021 at 16:27
I'm Learning more each day. To address the extra spacing in my fractions (comment above), I learned that # ?/??? is an acceptable format string and will display the numerator w/o prepending any padding. 1 1/128 vs 1 1/128.
– Anthony
Commented Jul 16, 2021 at 1:38

Add a comment |

1 Answer 1