Creating unique "User Name"

**swiss-cheese** · 10-28-2012, 06:42 PM

Have an excel spreadsheet with email addresses (and lots of them). This list is an old backup. Now I have a new membership site which allows me to use Excel to import members. However, I need to have the data in "import" columns so it can be mapped correctly to the new software. I can do that fairly easily but the column which is problematic is the User Name column. The new software requires one whereas the old backup software didn't so I don't have a unique user name for each person's email address. I was thinking I could do username@domain.com and simply harvest the "username" from the email address as the username. I cannot use the email address due to other restrictions in the software. Is there an easy routine to create a column with just the first part of the email address listed and dropping the @domain.com? Of course, none of the email addresses are from the same domain--their all random except for the "at" symbol. I have roughly 60,000 email addresses. Any ideas?

**Moo the Dog** · 10-28-2012, 06:51 PM

if your list of email addresses starts in A1, use this and fill down:

=LEFT(A1,SEARCH("@",A1)-1)

-Moo

**swiss-cheese** · 10-28-2012, 07:04 PM

Cool. Thanks, Moo. That is awesome! You saved the day!!! That's going to work.

However, now I realized I left off the most important detail in my post above. I'm an excel novice. I've never used a formula before this instant. I have no idea what you mean by the solution you gave.

I have all of the email addresses starting in A1, as you recommended. I copy-n-pasted that lovely code you gave me into B1 and it does just as I need it to do. However, then I get lost. I understand I need it to be =LEFT (A2, SEARCH ("@", A2)...something but where do I put it and how does it work? I'm confused how do I get it to automatically do it for all of the B column?

**Moo the Dog** · 10-28-2012, 07:07 PM

Well, once it is entered in cell B1, select that cell. At the bottom right corner of the cell should be a very small square. If you double-click on that square, it should fill down to match the entire column. If it doesn't, you can select that cell and click on "Edit", then "Fill Down". Should do the trick.

If neither of those options work, then click and hold on that small square at the bottom-right corner and drag down column B to the end of your list.

- Moo

**swiss-cheese** · 10-28-2012, 07:15 PM

Moo, You are awesome! That totally worked and saved me hours and hours of manual labour. Now you make me want to learn all of these great functions Excel can do. Thanks. Dancing a party dance!!!!!

**Moo the Dog** · 10-28-2012, 07:32 PM

You're welcome, swiss-cheese.

There is a chance that you may encounter duplicates in your list, and thus - not unique - if you are using only the part before the @ for usernames. It's a small chance, but a chance none the less. One way around that is concatenation of a value that would make each username unique. Let me know if you have any issues and I could make a few suggestions.

- Moo

- - - - - - - - - -
If you're issue has been solved, please be sure to click on 'Thread Tools' above your first post in this thread, and select "Mark thread as solved..."
Also, a good way to thank those who have helped is to click on that little star to the lower left of their name in one of their posts. If you don't, the Mods withhold our food rations. And remember, only YOU can prevent forest fires.