How can I enter and display construction measurements?

I'm creating a report for field workers to measure site conditions. They use their tablets to enter the measurements into the report which updates the Smartsheet for the job. We use standard construction measurements. Is there any way to display and enter the measurements in contruction notation, e.g. 126 3/16" or 5 3/4". I'm trying to avoid them having to have a calculator to convert those to 126.1875" or 5.75". They could also enter them with a space between the integer and the fraction, but then it won't be data I can use to make calculations later. I'm trying to make this as fool proof as possible but also have usable data.

Tags:

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Would it work for you if you had a formula in a helper column that could pull the fraction, convert it to a decimal, then add the integer?

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    Hey @Bobert

    You need to use a long formula like the one below which uses logic to separate out things. A bit harder than it should be of course but it gets the job done.


    =VALUE(LEFT(Length@row, FIND(" ", Length@row) - 1)) + VALUE(LEFT(LEFT(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1)), LEN(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1))) - 1), FIND("/", LEFT(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1)), LEN(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1))) - 1)) - 1)) / VALUE(SUBSTITUTE(LEFT(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1)), LEN(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1))) - 1), LEFT(LEFT(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1)), LEN(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1))) - 1), FIND("/", LEFT(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1)), LEN(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1))) - 1))), ""))
    
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Here is a little bit of a shorter formula. Apologies for the delayed response. I was taking an extended vacation in between the holidays.


    =VALUE(LEFT(Length@row), FIND(" ", Length@row) - 1)) + (VALUE(MID(Length@row, FIND(" ", Length@row) + 1, FIND("/", Length@row) - (FIND(" ", Length@row) + 1) / VALUE(MID(Length@row, FIND("/", Length@row) + 1, FIND(""", Length@row) - (FIND("/", Length@row) + 1)))

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Would it work for you if you had a formula in a helper column that could pull the fraction, convert it to a decimal, then add the integer?

  • Bobert
    Bobert ✭✭✭✭

    What kind of formula could I use to pull the integer and fraction without mistaking the numerator or denominator for an integer?

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    Hey @Bobert

    You need to use a long formula like the one below which uses logic to separate out things. A bit harder than it should be of course but it gets the job done.


    =VALUE(LEFT(Length@row, FIND(" ", Length@row) - 1)) + VALUE(LEFT(LEFT(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1)), LEN(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1))) - 1), FIND("/", LEFT(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1)), LEN(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1))) - 1)) - 1)) / VALUE(SUBSTITUTE(LEFT(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1)), LEN(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1))) - 1), LEFT(LEFT(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1)), LEN(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1))) - 1), FIND("/", LEFT(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1)), LEN(RIGHT(Length@row, LEN(Length@row) - FIND(" ", Length@row + 1))) - 1))), ""))
    
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Here is a little bit of a shorter formula. Apologies for the delayed response. I was taking an extended vacation in between the holidays.


    =VALUE(LEFT(Length@row), FIND(" ", Length@row) - 1)) + (VALUE(MID(Length@row, FIND(" ", Length@row) + 1, FIND("/", Length@row) - (FIND(" ", Length@row) + 1) / VALUE(MID(Length@row, FIND("/", Length@row) + 1, FIND(""", Length@row) - (FIND("/", Length@row) + 1)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!