# 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. ## 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.

then the return should be: (914) 760-XXXX

2. ## Re: Suppress #VALUE! error

Try using this formula

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

3. ## 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. ## 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. ## Re: Suppress #VALUE! error

Excellent - both options offered allow the desired outcome.

#### Posting Permissions

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