| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Remove characters | Excel Discussion (Misc queries) | |||
| Remove last 10 characters | Excel Discussion (Misc queries) | |||
| Remove first few characters | Excel Worksheet Functions | |||
| Remove top bit characters | Excel Discussion (Misc queries) | |||
| I need to remove characters ... | Excel Discussion (Misc queries) | |||
Linear Mode
