Finding rows where an <error> occurs in column formula

2 followers
0
Avatar

Any way to find which rows exhibit an <error> in a formula ( filter, etc)

For instance, in the below . A conversion of CMCIF_ ZIP to integer gave an <error> ( formula in ZIP_AS_INT) because of badly formed zip. Would like to get all rows which have <error> in this column. Thanks 

Marc Ellis

1 comment

  • Avatar
    Joel Stewart

    Hi Marc, there is not a function that isolates rows based on the state of an error or not. However, if you can understand the different types of errors that you are encountering as the cause, you can setup boolean checks for those items. 

    For example, the record "TX 75201" cannot be converted to an Integer because it contains a letter. I could convert this into a test for non-number characters (other than a period) like this: 

    LEN(REPLACEALL(#Sheet!Column;"[0-9.]";""))==0

    Using this function or a similar one can let you convert the error causes into a boolean that can easily be filtered for.

     

    0
    Comment actions Permalink
Please sign in to leave a comment.