Feet and inches conversion error if no foot value.

[atdemeo]

Hello,

I am using the below formula to take foot and inch measurements and round them to the nearest inch.

I had to combine several formulas to get it to work, so I am sure it's probably more complicated than it needs to be.
That said, I get an error of #value when there are no feet, only inches in the measurement column.

Please Login or Register  to view this content.

Here's what it does in the table:
1'4.035" ===== 1'4"
15'8.9" ===== 15'9"
25'0" ===== 25'0"
33'7.762" ===== 33'8"
-41'7.73" ===== -41'8"
54'9.21" ===== 54'9"
-68'5.073" ===== -68'5"
81'10.194"===== 81'10"
10.398" ===== #VALUE!
-5.035" ===== #VALUE!
68'5.073" ===== 68'5"
92'.398" ===== 92'0"

My thanks and appreciation to anyone who can modify the formula to function properly when there is no footage present. Bonus points for reducing its complexity.

File attached for reference.

Thanks!

[JohnTopley]

Try

=IFERROR(LEFT($A2,FIND("'",$A2)),"")&IFERROR(ROUND(SUBSTITUTE(TRIM(MID($A2,FIND("'",$A2)+1,255)),"""",""),0),ROUND(SUBSTITUTE($A2,"""",""),0)) & """"

[tim201110]

if 54'11.91" then 54'12", but right is 55'

[JohnTopley]

@tim: good catch!

[JohnTopley]

This will give result in Inches (in "helper" column ?)

=IFERROR((LEFT($A2,FIND("'",$A2)-1)+0),0)*12+IFERROR(ROUND(SUBSTITUTE(TRIM(MID($A2,FIND("'",$A2)+1,255)),"""","")+0,0),ROUND(SUBSTITUTE($A2,"""","")+0,0))

then

=INT(result/12) for feet

=MOD(result,12) for inches

No simple formula.

[Haseeb Avarakkan]

May be,

=IFERROR(LEFT(A2,FIND("'",A2)-1),0)&"'"&ROUND(SUBSTITUTE(MID(A2,IFERROR(FIND("'",A2)+1,1),20),CHAR(34),"")+0,)&CHAR(34)

[atdemeo]

May be,

=IFERROR(LEFT(A2,FIND("'",A2)-1),0)&"'"&ROUND(SUBSTITUTE(MID(A2,IFERROR(FIND("'",A2)+1,1),20),CHAR(34),"")+0,)&CHAR(34)

Thanks for your help guys. Haseeb is a step closer to what I am looking for. Only problem with this is that the negative measurements without footage show up as 0'-##" instead of -0'##"

Any thoughts on this?

Thanks again

-A

[AliGW]

Will this work for you?

=IFERROR(LEFT(A2,FIND("'",A2)-1),IF(LEFT(A2,1)="-","-0",0))&"'"&ABS(ROUND(SUBSTITUTE(MID(A2,IFERROR(FIND("'",A2)+1,1),20),CHAR(34),"")+0,))&CHAR(34)

[atdemeo]

That is is!

Thank you all for your help!

[AliGW]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[atdemeo]

Yep! Solved indeed.

Thanks!

[JohnTopley]

Try

92'11.5"

result

92' 12"

See post #3!

[AliGW]

Good point, John!!!

[AliGW]

How about this?

=IFERROR(IF(ABS(ROUND(SUBSTITUTE(MID(A2,IFERROR(FIND("'",A2)+1,1),20),CHAR(34),"")+0,))=12,1,0)+LEFT(A2,FIND("'",A2)-1),IF(LEFT(A2,1)="-","-0",0))&"'"&IF(ABS(ROUND(SUBSTITUTE(MID(A2,IFERROR(FIND("'",A2)+1,1),20),CHAR(34),"")+0,))=12,0,ABS(ROUND(SUBSTITUTE(MID(A2,IFERROR(FIND("'",A2)+1,1),20),CHAR(34),"")+0,)))&CHAR(34)

[JohnTopley]

Hence my reply #5.

[AliGW]

See post #14.

[AliGW]

Actually, on reflection, I think it needs to be this:

Please Login or Register  to view this content.

which will take care of a value such as -11.935".

[JohnTopley]

@Ali: very good!!!

[AliGW]

Thanks, John!!! You'd think I'd got nothing better to do ... devil2.gif

[JohnTopley]

.@Ali:..good brain training: plus you are a lady who likes a challenge!

[AliGW]

Yes, it certainly keeps the little grey cells active. Trouble is, it is addictive, and the ironing isn't going to do itself ... juggle.gif

[Haseeb Avarakkan]

May be this,

Please Login or Register  to view this content.

[tim201110]

my two cents
UDF can be understood more easily

Please Login or Register  to view this content.

[AliGW]

May be this,

Please Login or Register  to view this content.

Works, but does not return the format that the OP seems to want. For example, this:

9'11.5"

should become this:

10'0"

instead of this:

10'

It doesn't work for a value like -11.935" which should resolve to -1'0" (not 1'0").

[atdemeo]

I'm just getting back to this everyone, will report back shortly!

Thanks for all your help!

[atdemeo]

Actually, on reflection, I think it needs to be this:

Please Login or Register  to view this content.

which will take care of a value such as -11.935".

I think Ali has done it (again!)

Looks like everything is calculating correctly. Thank you so much for all your help!

[AliGW]

It was addictively fun - thank you!!!