Try ..instead of blank ("") refer the cell itself...

=IF(ISERROR(FIND("&",A2)),A2,TRIM(MID(A2,FIND("&", A2)+1,LEN(A2))))

If this post helps click Yes
---------------
Jacob Skaria


"Imajica12345" wrote:

Kool, that worked!!

What would the formula be if i wanted to remove anything after the & but if
a & does not exist, not to do anything.

TiA


"Jacob Skaria" wrote:

You can acheive this using a formula. With your data in ColA. try this
formula in B1. and copy this down as required...

=IF(ISERROR(FIND("&",A1)),"",TRIM(MID(A1,FIND("&", A1)+1,LEN(A1))))

If this post helps click Yes
---------------
Jacob Skaria


"Imajica12345" wrote:

is there a way to program a macro to remove all characters before "&" in a
cell, or range of cells? At the same time, if a cell does not contain a "&"
to delete the contents.

Ex.

Mike & Jill would become Jill
Mike would be come ""