How can I enter and display construction measurements?

Bobert

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.

Paul Newcome

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

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

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)))

Paul Newcome

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

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

Devin Lee

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

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)))