MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Extracting domains from a list of email addresses

  Asked By: Vivek    Date: Oct 21    Category: MS Office    Views: 811

I have a list of email addresses, and I need to find what all the
domains are and how often each domain occurs. I can do everything
in excel once I strip everything but the domain from the email

I wrote this to look at each address and delete each character until
it gets to an @, at which point it pastes what it has in the next
column and advances to the next email address.

I'm doing something wrong with my string handling though, as I'm
getting an "Invalid Qualifier" messege when I get to

If myEmail.substring(Char, 1) <> "@" Then

Can anyone point out what I'm doing wrong?

Dim myEmail As String
Dim Row As Integer
Dim TotalRow As Integer
Dim Char As Integer
Dim Counter As Integer

Row = 1
Char = 1

TotalRow = ActiveSheet.UsedRange.Rows.Count
For Row = 1 To TotalRow Step 1

For Counter = 1 To 30 Step 1
Email = Cells(Row, 1).Value

If myEmail.substring(Char, 1) <> "@" Then
myEmail.substring(Char, 1) = ""

Counter = Counter + 1
Char = Char + 1

End If

Next Counter

Cells(Row, 3).Value = Email

Next Row

End Sub



3 Answers Found

Answer #1    Answered By: Aberto Rossi     Answered On: Oct 21

Don't know whether this helps, but a much quicker way of splitting up
your addresses in Excel is to select the column and use the "text to
column" feature under the "data" menu. Just set the delimiter character
to other and put in the @. (Make sure the column to the left of the one
containing your addresses is blank.) The first part of the address  will
be in the first column, and everything after the @ will appear in the
second column.

Answer #2    Answered By: Nina Garcia     Answered On: Oct 21

i dont know what is going on with your code, but i think using function like len
and mid may be easier. for example, you can loop this, but the logic would be

characternumber = len(cells.value) --this counts the number of characters in
your domain
for (row 1 to end)
characternumber = 0
counter = 0
for i = 1 to characternumber
if left(cell.value,i)=@
counter = counter  +1 (counter should be set to 0 at the beginning of
each row  loop)
exit for
else: counter = counter +1
end if
cells(1,2).value = mid(cell.value,counter,characternumber - counter) (i don't
know if the beginning of this code is correct, but what i'm trying to do is put
the domain  name in the cell to the right of the current one)

the mid may be off by one character  on either the first or second integer
numbers, which you can fix by adding a +1. you'll just need to experiment.

Answer #3    Answered By: Wilbert Patterson     Answered On: Oct 21

I know you have had other replies, but ...

Notice that all the Excel functions are given one or more capital letters by the
VBA editor.

Notice that substring hasn't got any. I.e. substring is not recognised. Get in
the habit of noticing this, as it's a dead giveaway.

Didn't find what you were looking for? Find more on Extracting domains from a list of email addresses Or get search suggestion and latest updates.