+ Reply to Thread
Results 1 to 4 of 4

Replacing Errors when a Function Already Exists

  1. #1
    Registered User
    Join Date
    04-25-2013
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010 (Mac)
    Posts
    4

    Replacing Errors when a Function Already Exists

    Hi, I have a spreadsheet whereby there is column A and I have created a macro to insert column B, insert the following function and drag it down:

    =LEFT(A4,SEARCH("Cat",A4)-1)

    This way, if in column A I have a sentence such as "Black cat" the only thing that remains is "Black". And this function is in B1:B20

    However some of the values from A1:A20 are written such as "22-01-13 Monday" so as they change every week, the cells are not formatted as Date, and there is no consistent space in between the cells that hold the two or more dates. These values give the error #VALUE!, however, I want these cells to display the dates that are in column A, rather than the error.

    To no avail I tried the following function, placed in B5:

    =IF((A5,SEARCH("Cat",A5), (LEFT(A5,SEARCH("Cat",A5)-1), A5)))

    And this is what I'm trying to tell excel to do with it: if you find "Cat" in A5, leave any of the words left of "Cat", if you don't find "Cat" make B5 = A5

    Also, I know that there is an ISERROR function however I do not know how to use it for what I want as there is already a function in the cells.

    How would I got about this? I'm also open to creating a macro

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Replacing Errors when a Function Already Exists

    Maybe this...

    =IFERROR(LEFT(A5,SEARCH("Cat",A5)-1),A5)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    04-25-2013
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010 (Mac)
    Posts
    4

    Re: Replacing Errors when a Function Already Exists

    Great, works perfectly - thanks Tony!

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Replacing Errors when a Function Already Exists

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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