# Converting measures with fraction

Options

Hi all,

I am having a hard time to se a formula that converts this measure format 10' - 4 1/2" to Feet without fraction. The trick part is one of my parameter wont have the fraction "10' - 9"".

Could I please have some help.

Thanks a lot.

Bruno Gazire

• ✭✭✭✭✭✭
Options

I used helper columns to split out the numbers. For a reason I do not yet understand, I could not get the Inches to appear as value in the main formula column named Inches but needed to add an additional helper column to turn that number from text to value.

Feet: =VALUE(LEFT(Measure@row, FIND("'", Measure@row) - 1))

Inches:

=IF(CONTAINS("/", Measure@row) = "TRUE", VALUE(MID(Measure@row + " ", FIND("~", SUBSTITUTE(Measure@row + " ", " ", "~", 2)) + 1, FIND("~", SUBSTITUTE(Measure@row + " ", " ", "~", 3)) - FIND("~", SUBSTITUTE(Measure@row + " ", " ", "~", 2)) - 1)), MID(Measure@row, FIND("- ", Measure@row) + 1, LEN(Measure@row) - FIND("- ", Measure@row) - 1))

(When I added "Value" to the false portion of the if statement above, it returns Invalid)

[Credit goes to @Leibel S to find text between spaces. https://community.smartsheet.com/discussion/98219/how-do-i-return-text-from-text-string-with-the-space-as-a-delimiter]

Inches Value: =VALUE(Inches@row)

Fraction: =IF(CONTAINS("/", Measure@row) = "TRUE", VALUE(MID(Measure@row, FIND("/", Measure@row) - 1, 1)) / VALUE(MID(Measure@row, FIND("/", Measure@row) + 1, 1)), 0)

I hope these work for you!

• Options

Hi @ker9,

Thanks a lot! Also thanks to @Leibel S for the insight.

Have a great week! Bests,

Bruno Gazire

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!