remove all characters before a certain one
 

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

remove all characters before a certain one
 

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

remove all characters before a certain one
 

Hi

=IF(NOT(ISERROR(FIND("&",A1))),MID(A1,FIND("&",A1) +2,255),"")

--
Regards
Roger Govier

"Imajica12345" wrote in message
...
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 ""

remove all characters before a certain one
 

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


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

remove all characters before a certain one
 

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



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

--

Dave Peterson

remove all characters before a certain one
 

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

remove all characters before a certain one
 

=IF(ISERROR(FIND("&",A1)),A1,TRIM(MID(A1,1,FIND("& ",A1)-1)))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


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

remove all characters before a certain one
 

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