remove all characters before a certain one

I

Imajica12345

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 ""
 
J

Jacob Skaria

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
 
L

Luke M

This is setup to run on whatever cells you have selected upon activation

Sub SpecialFind()

'Note you could change Selection to
'something like Range("A2:A10")
For Each Cell In Selection
If Cell.Value Like "*&*" Then
Cell.Replace What:="*&", Replacement:=""
Else
Cell.Value = ""
End If
Next
End Sub
 
D

Dave Peterson

If you could use a helper column, you could fill it with formulas like:
=TRIM(MID(A1,SEARCH("&",A1&"&")+1,LEN(A1)))
 
I

Imajica12345

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
 
J

Jacob Skaria

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
 
Top