Returning an "Error" if specific character is typed into a cell

**cheddarthief** · 05-26-2011, 10:28 AM

I have the following formula:

=IF(ISERROR(LEFT($L22,FIND("'",$L22)-1)+((MID(L22,FIND("'",$L22)+1,LEN($L22)-FIND("'",$L22)-1))/12)), 0, LEFT($L22,FIND("'",$L22)-1)+((MID(L22,FIND("'",$L22)+1,LEN($L22)-FIND("'",$L22)-1))/12))

This formula looks at a particular cell, in this case cell L22, and takes a value that is shown in FT & fractional inches and converts it to the decimal ft equivalent. Ex: 10' 8 3/8" will return a value of 10.6979. I can then use this value for all my calculations.

The problem is that some people have a habit of typing the exact same value above but in the following format: 10'-8 3/8". Notice the "-" shown to show seperation from ft and inches. Unfortunately, if this is typed in this format, the formula above will see this as a minus calculation and the value returned is 9.3021 which obviously creates disasterous calculation problems.

Is there a way to modify the original formula to ignore the "-" character OR if the "-" character is typed in the cell an "ERROR" value will be returned instead?

Thanks in advance.

CheddarThief

**romperstomper** · 05-26-2011, 11:05 AM

=IF(ISNUMBER(SEARCH("-",$L22)),"Do not type minus signs in the cell!!",IF(ISERROR(LEFT($L22,FIND("'",$L22)-1)+((MID(L22,FIND("'",$L22)+1,LEN($L22)-FIND("'",$L22)-1))/12)), 0, LEFT($L22,FIND("'",$L22)-1)+((MID(L22,FIND("'",$L22)+1,LEN($L22)-FIND("'",$L22)-1))/12)))

**cheddarthief** · 05-26-2011, 04:13 PM

romperstomper,
That worked great. However, after applying it to the spreadsheet, I realize that the cell this formula resides in (P22) is actually in a column that is hidden to the users. It is merely there to perform a calculation so therefore the "Do not type minus signs in the cell" error will not be seen. This made me think, is there a way to put a formula in the actual cell (L22) that in the case where someone 'tries' to type a "-" character in the cell that it returns your error above rather? This will make the error immediately noticable in the actual cell. Is this possible or am I just dreaming?

**NBVC** · 05-26-2011, 04:25 PM

You can use Data Validation on that cell, so that if the user types anything with a dash they will get the error...

While in L22, go to Data|Validation and from the Allow menu select Custom then enter formula:

=ISERROR(SEARCH("-",L22))

You can go to the Error Alert tab to customize the message... and you can even go to the Input Message to have a popup message come up to tell them what they should enter