Using TEXT function with fractions

[Fujirich]

I need to use fractional numbers in a text string, but I'm having some formatting problems that I could use some help with.

I have some dimensions that are best expressed with fractions of an inch; like 31 1/2" or 40 17/32". The maximum places I will need in the fraction will be two, in other words 00/00. But there will be many times when one place will be all that is needed; like 0/0 - and that's where the problem starts.

If I use the TEXT function like this: TEXT(value,"0 00/00"), it works fine if my number has two places in each fraction; like 19 23/32. But if the number is 24 5/8, it will display as: 24 05/08. If I shorten the function to: TEXT(value,"0 0/0"), then Excel rounds off any two digit fractions to their nearest single digit equivlent. 19/32 becomes 3/5.

Is there any way to accomodate both conditions using this function? Or is there another way to do this?

Thanks in advance for any help...

Rich

 

[Seti]

This should give you what you want:

TEXT(value,"0 0/#0")

 

[Fujirich]

Seti - thanks so much, works great!

Rich

 

[Shelly D]

I had success using this : TEXT(value,"0 #/#"), to not have a fraction if none was required, for addresses. Using Excel 2016 for Mac.

 

[dkopp3]

Thanks so much! I was trying to modify the format that excel has as the default for fractions which is # ??/??. Yours works so much better though. I ended up doing this: ##-##/##''. I was trying to get it so that the inches symbol ('') would stay no matter what the number came out to be. I changed your 0 to ## so that if the whole number does come out to be 0 it will not show up. The - is just my desired formatting and doesn't change the code.