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
Bookmarks