Results 1 to 5 of 5

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

Threaded View

cheddarthief Returning an "Error" if... 05-26-2011, 10:28 AM
romperstomper Re: Returning an "Error" if... 05-26-2011, 11:05 AM
cheddarthief Re: Returning an "Error" if... 05-26-2011, 04:13 PM
NBVC Re: Returning an "Error" if... 05-26-2011, 04:25 PM
cheddarthief Re: Returning an "Error" if... 05-26-2011, 05:16 PM
  1. #1
    Forum Contributor
    Join Date
    02-20-2010
    Location
    Youngstown, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    182

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

    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
    Last edited by cheddarthief; 05-26-2011 at 05:17 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1