Excel 2002 - ReFormat DATA

[mateo107]

The title might not be the best to describe what I need, but I can't
think of the words to describe what I need. Here goes the long
version.

I have a spreadsheet that has ~20,000 rows in it. In one column, (B),
I have a list of email addresses. What I need to do, somehow, is scan
each of the lines and pull out all of the possible domain names that
are there. I don't want a count of each time each domain name is
listed, but if there is a domain name listed, it needs to be added to
another "master list".

So, for example, I may have data like this.

Row # | EMail Address | Misc. Other Data
1 | (e-mail address removed) | other stuff
2 | (e-mail address removed) | other info
3 | (e-mail address removed) | miscellaneous data
4 | (e-mail address removed)
etc...etc...

Then (perhaps on a sheet called "DOMAINS" (which I've already made and
labelled)) I'd like to have this master list show up on another sheet.
For the example above, I'd like my list to show:

Row #1 | Domain Name
1 | test.com
2 | yahoo.com
3 | some.domain.com
(notice how test.com was already there, so we dont' need to add it
again).

I am VERY comfortable with VB and VBA scripting, so anything and
everything you could give me would be greatly appreciated.

Thank you!
Any Questions about possible ambiguities due to my writing, please post
back!

 

[Guest]

In a column adjacent to your list, use the following formula to extract the
domain name for each record..=RIGHT(A1,FIND("@",A1)+1) (where a1 is the
email address).

Create a PivotTable that includes this column, and add the column (and only
the column) to the Row Field. That should get you a list of unique domain
names.

Use the edit menu to copy selected items....that's it...gotta run!!
--
Hope this Helps!
Camilo

Objective: To help one person each day!

Feedback is greatly appreciated! Please let me know if I've answered your
question or if my post was helpful to you?

 

[mateo107]

thanks for such a quick reply, but this isn't quite working.. here ar
the results I get...

(before adding to a pivot table)

(e-mail address removed) |[email protected]
(e-mail address removed) | (e-mail address removed)
(e-mail address removed) | (e-mail address removed)
(e-mail address removed) | (e-mail address removed)
(e-mail address removed) | (e-mail address removed)
(e-mail address removed) | (e-mail address removed)
(e-mail address removed) | (e-mail address removed)

(names are ficticious)

So, perhaps we need to break it down this way...

So, I'm thinking, Because each domain name may be each of varyin
lengths, would we need to first "strip off' everything after (an
including the @). So , I guess that's the code I would need.... On
that takes into acct. the varying length of the domain name (everythin
after the @ symbol), then does the "RIGHT" code, to remove everything t
the left of the @ symbol.

Make sense

 

[Guest]

ooops..here you go! =RIGHT(A1,LEN(A1)-FIND("@",A1))



--
Hope this Helps!
Camilo

Objective: To help one person each day!

Feedback is greatly appreciated! Please let me know if I've answered your
question or if my post was helpful to you?