Converting from feet and inches to inches

jmparra

Hi All,

I need to convert from feet and inches (5' 4") to inches only in smartsheet.  I have tried using 

=LEFT(Width3,FIND("'",Width3)-1)*12+SUBSTITUTE(MID(Width3,FIND("'",Width3)+1,LEN(Width3)),"""","")

=LEFT(Width3, FIND("'",Width3)-1)*12 + MID(Width3,FIND("'",Width3)+1,LEN(Width3)-FIND("'",Width3)-1)

=IF(ISBLANK(Width3), " ",IFERROR(IFERROR(CONVERT(LEFT(Width3,FIND("'",Width3)-1),"ft","in"),0)+IFERROR(SUBSTITUTE(RIGHT(Width3,LEN(Width3)-IFERROR(FIND("'",Width3),0)),"""","")+0,0),Width3))

Any thoughts on how to make this work in smart sheet would be greatly appreciated.  I don't know what else to try.

Thanks,

-Jen

Kara Lumley

Hi Jen,

Try this:

=VALUE(LEFT([Column1]1, 1)) * 12 + (VALUE(RIGHT([Column1]1)))

Best,

Kara

Paul Newcome

Kara's solution unfortunately will not work in this particular case because of the possibility of extra digits in the feet section and the quote used as the symbol for inches.

 

The VALUE(LEFT(.....)) portion will only pull the first digit of the feet. This will work for 0 - 9, but if they have 10 feet it will only calculate for 1.

 

The VALUE(RIGHT(.....)) will not work because the right most character is a symbol as opposed to a number.

 

Assuming there will always be a space between the feet and inches, to always pull the correct data from the entry, you will need something along the lines of...

 

=VALUE(LEFT(Width@row, FIND("'", Width@row) - 1))

to pull the feet (this will look for the apostrophe used as the symbol for feet as the base for how many digits from the left to pull).

Then Multiply by 12

 

=VALUE(LEFT(Width@row, FIND("'", Width@row) - 1)) * 12

For the inches...

=VALUE(RIGHT(SUBSTITUTE(Width@row, """, ""), LEN(Width@row) - FIND(" ", Width@row)))

will essentially remove the quote used as the inches symbol, then subtract the number of characters in the space is from the total number of character to determine exactly how many digits from the right (minus the ") to pull.

Adding those two together will give you what you need.

=VALUE(LEFT(Width@row, FIND("'", Width@row) - 1)) * 12 + VALUE(RIGHT(SUBSTITUTE(Width@row, """, ""), LEN(Width@row) - FIND(" ", Width@row)))

.

So in your example above, you have 5' 4". We will break it down into character position to help reference exactly what this formula is doing.

Position 1 = 5

Position 2 = '

Position 3 =  (space)

Position 4 = 4

Position 5 = "

.

So to pull the correct number of digits for the LEFT function to find the feet, we use the FIND function to determine where the apostrophe is (Position 2) and then subtract 1 from that. That will give us the total number of digits BEFORE the apostrophe regardless of how many there are.

The next step is the easiest step... Multiple that result by 12. We wrapped the LEFT function in a VALUE function to ensure that an actual number is pulled as opposed to a text string representing a number. That way we can use it in future calculations.

Now for the fun part... Pulling the inches.

There are two ways this can be accomplished.

One: As explained above.

Two: You could use a MID statement along the lines of...

 

MID(Width@row, FIND(" ", Width@row) + 1, (FIND(""", Width@row) - FIND(" ", Width@row) -1))

The breakdown for the MID statement goes something like this:

We want to look at the cell in the Width column in whatever row the formula is in.

We will start in the position after the space (First FIND to establish the space is in position three, then we add one which gives us position four which is the first digit of the inches).

To determine the number of digits we find the position of the quote (5) and subtract from it the position of the space (3) which gives us two digits to pull. We then subtract one more from that to get 1 digit.

To incorporate this into the formula instead of the SUBSTITUTE section, you would use the first portion

=VALUE(LEFT(Width@row, FIND("'", Width@row) - 1)) * 12

and then add the MID statement

=VALUE(LEFT(Width@row, FIND("'", Width@row) - 1)) * 12 + MID(Width@row, FIND(" ", Width@row) + 1, (FIND(""", Width@row) - FIND(" ", Width@row) -1))

.

Either way will work depending on your preference. Feel free to let me know if any of this doesn't make sense or needs further explaining.

Kara Lumley

Ha. Thanks Paul,

I thought my formula was a bit basic - it would only work for single digits and no spaces. This is great!

Best,

Kara

Paul Newcome

Happy to help.

 

Your formula would only work for single digits in the Feet measurement and no inches. Spaces shouldn't have an effect on your formula assuming they are between the rightmost and leftmost character.

 

The biggest issue is your RIGHT portion. Based on your formula you would always pull a " instead of any numbers as the " is used as the last character in the string to denote the inches measurement. You would probably get an invalid data error or something along those lines because of this. To account for that, you could always wrap the RIGHT portion of your formula in a SUBSTITUTE function to remove the " before looking at the right of the cell.

 

=VALUE(SUBSTITUTE(RIGHT.........), """, ""))

 

But this would still only work for single digits in both feet and inches.

The biggest reason I was able to catch all of this so quickly is because I personally ran into something VERY similar just the other day and ended up with with a very similar solution (after starting with a basic LEFT + RIGHT set-up Lol)

noclue

Can anyone help me with this? I'm trying to use this formula but the right side is not working.

346' 11"

I can get the left side to work but I'm not sure why the right side isn't working. Thanks!