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!

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!

"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

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!

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!