Formula to output 0' -0".

[OfficeUser]

This my current formula:
=IF(ISNUMBER(FIND("'",$R8)),LEFT($R8,FIND("'",$R8)-1))+(SUBSTITUTE(IF(ISERROR(SEARCH(" ",$R8)),"0 ","")&MID($R8,IF(ISNUMBER(FIND("-",$R8)),FIND("-",$R8)+1,1),8),"""","")&IF(ISERROR(SEARCH("/",$R8)),"/1",""))/12

It works very well. The problem is when R8 is a value of 0'- 0"

Then I get a #VALUE! in cell Q8 where this formula resides. I having been trying multiple things to simply have it show 0'- 0" but all have failed. Anyone have any suggestions? Thanks.

 

[T. Valko]

This my current formula:
=IF(ISNUMBER(FIND("'",$R8)),LEFT($R8,FIND("'",$R8)-1))+(SUBSTITUTE(IF(ISERROR(SEARCH(" ",$R8)),"0 ","")&MID($R8,IF(ISNUMBER(FIND("-",$R8)),FIND("-",$R8)+1,1),8),"""","")&IF(ISERROR(SEARCH("/",$R8)),"/1",""))/12

It works very well. The problem is when R8 is a value of 0'- 0"

Then I get a #VALUE! in cell Q8 where this formula resides. I having been trying multiple things to simply have it show 0'- 0" but all have failed. Anyone have any suggestions? Thanks.

Try one of these...

If you're using Excel 2007 or later...

=IFERROR(IF(ISNUMBER(FIND("'",$R8)),LEFT($R8,FIND("'",$R8)-1))+(SUBSTITUTE(IF(ISERROR(SEARCH(" ",$R8)),"0 ","")&MID($R8,IF(ISNUMBER(FIND("-",$R8)),FIND("-",$R8)+1,1),8),"""","")&IF(ISERROR(SEARCH("/",$R8)),"/1",""))/12,"0'-0""")

This one will work in any version of Excel...

=LOOKUP("zzz",CHOOSE({1,2},"0'-0""",IF(ISNUMBER(FIND("'",$R8)),LEFT($R8,FIND("'",$R8)-1))+(SUBSTITUTE(IF(ISERROR(SEARCH(" ",$R8)),"0 ","")&MID($R8,IF(ISNUMBER(FIND("-",$R8)),FIND("-",$R8)+1,1),8),"""","")&IF(ISERROR(SEARCH("/",$R8)),"/1",""))/12))

 

[Scott Huish]

Here's a much simpler formula:

=LEFT(R8,FIND("'",R8)-1)+(SUBSTITUTE(REPLACE(R8,1,FIND("-",R8)-1,""),"""","")/-12)

However I did not get an error with your formula assuming an entry of 0'-0"

Excel Workbook
	O	P	Q	R
8	0			0'-0"
9	0
Sheet1

 

[OfficeUser]

=LOOKUP("zzz",CHOOSE({1,2},"0'-0""",IF(ISNUMBER(FIND("'",$R8)),LEFT($R8,FIND("'",$R8)-1))+(SUBSTITUTE(IF(ISERROR(SEARCH(" ",$R8)),"0 ","")&MID($R8,IF(ISNUMBER(FIND("-",$R8)),FIND("-",$R8)+1,1),8),"""","")&IF(ISERROR(SEARCH("/",$R8)),"/1",""))/12))

This formula will output "0'- 0" regardless of what is in cell R8. Thanks.

 

[OfficeUser]

Here's a much simpler formula:
=LEFT(R8,FIND("'",R8)-1)+(SUBSTITUTE(REPLACE(R8,1,FIND("-",R8)-1,""),"""","")/-12)

This does work but does have its limitations. The formula I intially posted was due to possible variations. Sometimes a user inputs it as 13' 2-3/4" or 13' 2 3/4" or like it should be, 13'- 2 3/4". It was intended to work with all variations that I noticed. Thanks.

 

[Rick Rothstein]

Does this formula work the way you want?

=LEFT(A1,FIND("'",A1)-1)+SUBSTITUTE(SUBSTITUTE(MID(TRIM(A1),FIND("'",A1)+1,99),"-"," "),"""","")/12