How to convert a number with a fraction reading as text, to a number (3 2/3) to 3.66


I'm having an issue with incoming data being read as a text. I need it changed to read as a number so I can incorporate formulas. I've tried a few value formulas. The initial formula to pull the numbers from the original text is substitute function. Thanks in advance


  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @Tshaw ,

    You'll need to parse out the pieces, convert them to values and then put them together.


    =VALUE(LEFT([fraction text]@row, FIND("/", [fraction text]@row)-1)+(VALUE(MID([fraction text]@row, FIND("/", [fraction text]@row)+1),1))/VALUE(RIGHT([fraction text]@row, 1)))

    Change [fraction text] to your column name. It's set up to convert a 3 digit fraction including the /. I didn't test it so you might need to tweak it if you get an UNPARSABLE or other error.



    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!