if data is "A", then paste it to another cell
 

i have a list of 8000 e-mail addresses and i need to remove 700 of them that
contain a certain address (ex: @yourcompany.com). Does anyone know how to
create a formula that would look for them, then remove them from their
original cell and move them to a different cell???

Thansk!

if data is "A", then paste it to another cell
 

You can add another column to extract the webgsite from the rest of the email
address like this

=MID(H1,FIND("@",H1)+1,LEN(H1))

which will look for all text after the "@". Then sort on the new column.
You can then quickly remove certain groups of rows from the worksheet.

"mike m" wrote:

i have a list of 8000 e-mail addresses and i need to remove 700 of them that
contain a certain address (ex: @yourcompany.com). Does anyone know how to
create a formula that would look for them, then remove them from their
original cell and move them to a different cell???

Thansk!

if data is "A", then paste it to another cell
 

"mike m" wrote:

i have a list of 8000 e-mail addresses and i need to remove 700 of them that
contain a certain address (ex: @yourcompany.com). Does anyone know how to
create a formula that would look for them, then remove them from their
original cell and move them to a different cell???

Thansk!

I would do this "manually" rather than with VBA. Basically it would involve
sorting your entire data table by the address (the portion after the @). Then
simply cut/paste those containing your target (eg @yourcompany.com).

To sort by address you need to extract just the address into a new column.
It's easiest to describe in three steps (each a new column). Here are the
formulas, assuming the full email is in column A:

=FIND("@",A4) =LEN(A4) =RIGHT(A4,E4-D4)

When you've done that you will see just the portion after the @ sign in the
last column. Sort the entire datatable by that column, find your group, and
cut/paste. Delete the newly blank rows and you're there.

If you need to restore the original sort order, create a new column before
you start, and fill it with sequential numbers. Then you can sort on that
when you're done with the cut/paste. Be sure to make this column part of your
data table when you sort by address!

Art

if data is "A", then paste it to another cell
 

Hi Joel--1st THANKS!

2nd--could i get a little clarification pls?

Here's what i did:
highlight the column with data
paste formula in formula area
a new blue box pops up and i left where it is and i tried dragging over the
data cells
click enter
error

what am i doing wrong?
thanks!!!

"mike m" wrote:

i have a list of 8000 e-mail addresses and i need to remove 700 of them that
contain a certain address (ex: @yourcompany.com). Does anyone know how to
create a formula that would look for them, then remove them from their
original cell and move them to a different cell???

Thansk!

if data is "A", then paste it to another cell
 

THANKS ALL! IT WORKED!

"mike m" wrote:

Hi Joel--1st THANKS!

2nd--could i get a little clarification pls?

Here's what i did:
highlight the column with data
paste formula in formula area
a new blue box pops up and i left where it is and i tried dragging over the
data cells
click enter
error

what am i doing wrong?
thanks!!!

"mike m" wrote:

i have a list of 8000 e-mail addresses and i need to remove 700 of them that
contain a certain address (ex: @yourcompany.com). Does anyone know how to
create a formula that would look for them, then remove them from their
original cell and move them to a different cell???

Thansk!