how to convert inches to feet and inches expressed as 12'6"x12'6"
how to convert inches(150x150) to feet and inches expressed as 12'6"x12'6"
Answers
-
@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?
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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.
-
@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) + "''"
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
What should I do if I want to do the opposite.
Example: I have a data shown as 12' 4", and i want to convert it to inches in decimal. What should be the fromula?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!