Suppress #VALUE! error

[sbrotz]

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

 

[barry houdini]

Try using this formula

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

 

[rconverse]

You could also try:

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

 

[barry houdini]

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)))

 

[sbrotz]

Excellent - both options offered allow the desired outcome.