|
Excel 2002 - ReFormat DATA
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" wrote: 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 | | other stuff 2 | | other info 3 | | miscellaneous data 4 | 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! -- mateo107 ------------------------------------------------------------------------ mateo107's Profile: http://www.excelforum.com/member.php...o&userid=22299 View this thread: http://www.excelforum.com/showthread...hreadid=532115 |
Excel 2002 - ReFormat DATA
thanks for such a quick reply, but this isn't quite working.. here are the results I get... (before adding to a pivot table) | | | | | | (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 varying lengths, would we need to first "strip off' everything after (and including the @). So , I guess that's the code I would need.... One that takes into acct. the varying length of the domain name (everything after the @ symbol), then does the "RIGHT" code, to remove everything to the left of the @ symbol. Make sense? -- mateo107 ------------------------------------------------------------------------ mateo107's Profile: http://www.excelforum.com/member.php...o&userid=22299 View this thread: http://www.excelforum.com/showthread...hreadid=532115 |
Excel 2002 - ReFormat DATA
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? "mateo107" wrote: 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 | | other stuff 2 | | other info 3 | | miscellaneous data 4 | 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! -- mateo107 ------------------------------------------------------------------------ mateo107's Profile: http://www.excelforum.com/member.php...o&userid=22299 View this thread: http://www.excelforum.com/showthread...hreadid=532115 |
| All times are GMT +1. The time now is 01:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com