Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default 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 ""
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default 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 ""

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,480
Default 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 ""


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,722
Default 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 ""

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default 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 ""

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,722
Default 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 ""

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default 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 ""

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Remove characters Suraj Noorsai Excel Discussion (Misc queries) 5 July 25th 08 02:44 PM
Remove last 10 characters Supe Excel Discussion (Misc queries) 2 September 5th 07 10:46 PM
Remove first few characters coa01gsb Excel Worksheet Functions 5 March 23rd 06 02:48 PM
Remove top bit characters Brett... Excel Discussion (Misc queries) 8 February 9th 06 06:38 PM
I need to remove characters ... Rick Excel Discussion (Misc queries) 2 August 31st 05 06:50 PM


All times are GMT +1. The time now is 03:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"