Pull email addresses from SS, by domain, into CSV format.

[frank]

TIA for any help.

I have a SS of 1500 email addresses, all in the second column of a
five-column worksheet. I would like to pull a copy of all email
addresses which have domains that begin with the letter "e", for
example, out of the column and into a new file in Comma Separated
Value format. (So if there happens to be 25 email addresses in the SS
with domains that begin with the letter "e", then in my new CSV file
would have them all -- (e-mail address removed),[email protected],[email protected],
(e-mail address removed), etc.)

What's the best way to go about this?

 

[Bill Manville]

I have a SS of 1500 email addresses, all in the second column of a
five-column worksheet. I would like to pull a copy of all email
addresses which have domains that begin with the letter "e", for
example, out of the column and into a new file in Comma Separated
Value format.

Sounds like a job for custom autofilter.
Data / AutoFilter
Click the arrow at the top of column 2 and select custom
In the first dropdown select "contains". In the second dropdown enter
"@e" without the quotes and click OK

Then edit copy column 2 of the table and you will just get the visible
data. Paste into a new worksheet and save as csv.

If you wanted to do that for each letter of the alphabet you could
write a macro which might be something like this

Dim I as Integer
With Range("A1").CurrentRegion
.AutoFilter
For I = 1 To 26
.AutoFilter 2, "*@" & Char(Asc("a")+i-1) & "*"
WorkBooks.Add xlWorksheet
.Columns(2).Copy Range("A1")
ActiveSheet.SaveAs "Address" & Char(Asc("a")+i-1) & ".csv",
FileFormat:=xlCSV
ActiveWorkbook.Close False
Next I
.AutoFilter
End With

End With


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[frank]

Sounds like a job for custom autofilter.
Data / AutoFilter
Click the arrow at the top of column 2 and select custom
In the first dropdown select "contains". In the second dropdown enter
"@e" without the quotes and click OK

Then edit copy column 2 of the table and you will just get the visible
data. Paste into a new worksheet and save as csv.

If you wanted to do that for each letter of the alphabet you could
write a macro which might be something like this

Dim I as Integer
With Range("A1").CurrentRegion
.AutoFilter
For I = 1 To 26
.AutoFilter 2, "*@" & Char(Asc("a")+i-1) & "*"
WorkBooks.Add xlWorksheet
.Columns(2).Copy Range("A1")
ActiveSheet.SaveAs "Address" & Char(Asc("a")+i-1) & ".csv",
FileFormat:=xlCSV
ActiveWorkbook.Close False
Next I
.AutoFilter
End With

End With


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

That did it! Thank you, Bill.