how to convert inches to feet and inches expressed as 12'6"x12'6"

JBasks

how to convert inches(150x150) to feet and inches expressed as 12'6"x12'6"

Jeff Reisman

@JBasks I assume you're going to have many rows with different values in them, and want them dynamically displayed in the FF'II"xFF'II" format...

Are there any standards for your measurements? Like always more than 100 inches, less than 1000, or anything like that?

Arnold Guy

I have the same question.

Couldn't find the answer here: Smartsheet Functions Help Pages.

Please advise, because I have to convert every single result with online converter and it drives me crazy.

Jeff Reisman

@Arnold Guy Wow, this is an old thread! I have a better answer than I did last time.

First, you'll want to divide your number of inches by 12, and use INT function to get the integer portion as your feet measurement. That's the easy part.

=INT(Measurement@row / 12)

Say Measurement@row is 174, divided by 12 is 14.5, the result of the formula would be 14 feet.

The hard part is calculating the inches. Well, it's not difficult, just takes a few combined functions to get there from here.

=VALUE(MID((Measurement@row / 12), FIND(".", (Measurement@row / 12)), 5)) * 12

What we're saying here is find me the characters from the result of Measurement@row / 12, starting at the decimal point, and going out 5 places. Then convert that to a numeric value. Then multiply that numeric value by 12. If Measurement@row = 174, the result of this formula is 6 inches.

However, if there are fractions of an inch, you probably want to rein those decimal places in, so we'll add a ROUND function to keep the result down to 2 decimal places in the inches:

=ROUND((VALUE(MID((Measurement@row / 12), FIND(".", (Measurement@row / 12)), 5)) * 12), 2)

Now we put them together and add in the ' and '' marks:

=INT(Measurement@row / 12) + "'" + ROUND((VALUE(MID((Measurement@row / 12), FIND(".", (Measurement@row / 12)), 5)) * 12), 2) + "''"

Note: In this portion =INT(Measurement@row / 12) + "'" , that's an apostrophe surrounded by quotes. In the other half of the formula, it's two apostrophes surrounded by quotes.

So how do we do this with a Measurement value of 174x139?

If it were me, and I had plenty of room in the sheet, I would use helper columns (call them Left Value and Right Value) to separate the two numeric values, and then use the formula above on the values from each of those helper columns.

For the Left Value: =VALUE(LEFT(Measurement@row, (FIND("x", Measurement@row) -1))

This finds the position of "x" and then collects the characters to the left of the "x", and then converts them to numeric value.

For the Right Value: =VALUE(MID(Measurement@row, FIND("x", Measurement@row) + 1, 10))

This finds the position of "x" and collects everything for 10 characters to the right of it, then converts the characters to numeric value.

Now, we put it ALL together:

=INT([Left Value]@row / 12) + "'" + ROUND((VALUE(MID(([Left Value]@row / 12), FIND(".", ([Left Value]@row / 12)), 5)) * 12), 2) + "''" + "x" + INT([Right Value]@row / 12) + "'" + ROUND((VALUE(MID(([Right Value]@row / 12), FIND(".", ([Right Value]@row / 12)), 5)) * 12), 2) + "''"

But wait... what if there's no decimal values when you divide the initial measurement by 12? You'll get an INVALID OPERATION error! Now we add in some IFERROR functions to account for that. If there's an error, the system will replace the error message with 0:

=INT([Left Value]@row / 12) + "'" + IFERROR(ROUND((VALUE(MID(([Left Value]@row / 12), FIND(".", ([Left Value]@row / 12)), 5)) * 12), 2), 0) + "''" + "x" + INT([Right Value]@row / 12) + "'" + IFERROR(ROUND((VALUE(MID(([Right Value]@row / 12), FIND(".", ([Right Value]@row / 12)), 5)) * 12), 2), 0) + "''"