Results 1 to 5 of 5

Suppress #VALUE! error

This is a discussion on Suppress #VALUE! error within the Excel Questions forums, part of the Question Forums category; In the formula below, I am trying to find the word "Mobile:" within a text string contained in cell N5 ...

  1. #1
    Board Regular
    Join Date
    Jul 2004
    Posts
    120

    Default Suppress #VALUE! error

    In the formula below, I am trying to find the word "Mobile:" within a text string contained in cell N5 and if present, add 8 spaces from where it is found, then copy the next 14 characters. If "Mobile:" is not found, the cell should be blank. I know I am close, but not quite there. What am I doing wrong?

    =IF(MID(N5,FIND("Mobile:",N5)+8,14)="#VALUE!","",MID(N5,FIND("Mobile:",N5)+8,14))

    If N5 is: Residence: (845) 223-XXXX;Business: (845) 473-XXXX#1106,
    then the return should be a blank cell.

    If N5 is: Residence: (914) 686-XXXX;Business: (914) 592-XXXX;Mobile: (914) 760-XXXX;Business: (201) 986-XXXX #2076;Business: (908) 272-XXXX
    then the return should be: (914) 760-XXXX

  2. #2
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,829

    Default Re: Suppress #VALUE! error

    Try using this formula

    =MID(N5,FIND("Mobile:",N5&"Mobile:")+8,14)

  3. #3
    Board Regular
    Join Date
    Nov 2007
    Location
    Chicago, IL
    Posts
    1,161

    Default Re: Suppress #VALUE! error

    You could also try:

    =IF(IsError(N5,FIND("Mobile:",N5)+8,14)="true","",MID(N5,FIND("Mobile:",N5)+8,14))

  4. #4
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,829

    Default Re: Suppress #VALUE! error

    Indeed, rconverse, that's a more "standard" approach, although as written I don't think your formula will work because you haven't included "MID" in the first part.

    In fact, you can probably just check for an error in the FIND function....and you don't need "=TRUE", so that would be

    =IF(ISERR(FIND("Mobile:",N5)),"",MID(N5,FIND("Mobile:",N5)+8,14)))

  5. #5
    Board Regular
    Join Date
    Jul 2004
    Posts
    120

    Smile Re: Suppress #VALUE! error

    Excellent - both options offered allow the desired outcome.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com