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