Find Email Address in a text file

[matt4003]

Hello,

I am having a small issue. I received a text file from a co-worke
over seas that contains all his outlook contact information. I hav
opened it in excel using CSV. There are thousands of rows and anou
100 columns, problem is that the email address do not all fall in th
same column or row. I need to be able to locate each email address an
copy just the domain (@yahoo.com for example) into a singl
column.....does anyone have any suggestions??

Thanks,
Mat

 

[Ron de Bruin]

Hi Matt

Try this one

It will copy all Mail addressesfrom sheet1 to sheet2 in column A
You can use Data>Text to columns then to split them

Sub test()
Dim FirstAddress As String
Dim rng As Range
Dim I As Long
Dim Rcount As Long

Application.ScreenUpdating = False
Rcount = 0
With Sheets("Sheet1").Cells

Set rng = .Find(What:="@", _
After:=.Range("IV" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rng Is Nothing Then
FirstAddress = rng.Address
Do
Rcount = Rcount + 1
rng.Copy Sheets("Sheet2").Range("A" & Rcount)
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> FirstAddress
End If
End With
Application.ScreenUpdating = True
End Sub

 

[lpolliard]

Try this. When opening the text file in Excel set the delimiter to th
'@' sign. This will group the columns with the domain at the beginin
of the string. The first column I believe will be garbage to thro
out. Cut and paste your columns to column A. Unfortunatly if you hav
100 addresses on a single line then you will still have 100 columns.
custom VB macro could help with this situation. Using the Fin
function obtain the location of the first space which should occu
after the .com or .net or .org etc. Use this information and the Lef
function to obtain the information desired. Use Concatenation to tac
the @ back on. Hope this helps