Convert decimal to fraction

[pukenzz]

I know this question has been asked all over the place but I still am not finding the answer I am hoping for…

I want a cell formatted so that it outputs a fraction to the nearest 1/32 of an in or reduces it to the least common denominator. This should happen in the same cell I key it in. So it would be a number format not a cell formula.

It can round up or down to the nearest 32nd
Fraction As sixteenths (8/16) is not precise enough.
Fraction Up to two digits (21/25) does not provide a fraction that would be found on a tape measure.
Custom # ??/32 does not reduce the fraction

And I want it all to happen right in the cell I’m entering the number. I don’t want to have it converted via a formula and result in a different cell.

Also the number should have an “ mark on it. I don’t care how I have to input the number. I’m thinking it would be nice if 22 5/16” could be entered two or three possible ways… 22.3125 or =22+.3125 or 22+5/16.

 

[lrobbo314]

Nevermind.

 

[lrobbo314]

So if you enter 22.3125, is 22 10/32" the right answer?

 

[shg]

If you want the fraction reduced, you'll need to either use a formula in another cell, or VBA. You can't do it via formatting alone.

 

[tonyyy]

If you want the fraction reduced, you'll need to either use a formula in another cell, or VBA. You can't do it via formatting alone.

Have you tried...

# ??/??" inches"

The question marks reduce the fraction to the lowest common denominator, while the " inches" adds, well, " inches" rather than the " symbol.

Cheers,

tonyyy

 

[tonyyy]

Have you tried...

# ??/??" inches"

The question marks reduce the fraction to the lowest common denominator, while the " inches" adds, well, " inches" rather than the " symbol.

Cheers,

tonyyy

Or, if you want to get tricky, you could use...

# ??/??"''"

Where there are two apostrophe symbols between the quotes.

 

[shg]

Have you tried...

# ??/??" inches"

The question marks reduce the fraction to the lowest common denominator, while the " inches" adds, well, " inches" rather than the " symbol.

Cheers,

tonyyy

It doesn't round to 32nds, though:

Row\Col	A​	B​
1​	3.14​
2​	3 7/50 inches​	A2: =A1
3​		Format of A2: # ??/??" inches"

 

[tonyyy]

It doesn't round to 32nds, though:

Row\Col	A​	B​
1​	3.14​
2​	3 7/50 inches​	A2: =A1
3​		Format of A2: # ??/??" inches"

<tbody>
</tbody>

You're quite right, shg. I missed that part of the request when replying. But short of using vba or another cell for a formula, is there a way to use conditional formatting to detect non-32nd denominators (or their reductions) and then apply the # ??/32 format mask?

 

[shg]

No, there isn't.

 

[tonyyy]

Understood. Thanks!