Text to Column Conversion Wizard: Create Unique Email Addresses
In an effort to control incoming emails, a large computer hardware company has included email forms on its website, so that users’ emails are directed to a centralized location. Unfortunately, the format of the emails of its employees; it’s first name, underscore, last name @companyname.com — was common; so too easy. So customers Google searched for the company’s employee listings and, using this popular format, discovered how to send emails directly to individuals. It resulted in mass chaos.
Your job: Assign email addresses that are meaningful to employees, but not to the general public. After much deliberation, the new format is: User’s first and middle name, plus the user’s last name, plus the number of characters in the last name preceded by 700. So Lisa Valerie Kudrow would be [email protected] Note that email addresses are NOT case sensitive.
Use the convert text to columns wizard
1. Enter 10 or 15 names in column A (as if the names came from an ASCII or CSV text file). In this example, the names are in cells A2 through A13. Highlight this range.
2. Next, separate the names into three separate columns. To select Data > Text to Columns.
3. In the Text to Column Conversion Wizard dialog box, make sure the Delimited the button is checked / checked, then click Next.
Convert Text to Columns Wizard, select Delimited.
4. In step 3 above, note the options for the delimiters: Tab, semicolon, comma, space, other. Check the Space box — notice how Excel divides the columns in the preview window below — then click Next.
JD SartainFor delimiters, check the option in the Space box.
5. In step 4 above, note the options for the column data format: general, text, date, do not import column (ignore). Select the Text button. Click on the next column and select Text again, and the last column and Text again, etc. Finally, click on the To finish button.
JD SartainSelect the Text button for all three fields.
6. Note that Excel divides column A into three columns: A, B, and C. Adjust the column widths to match the text in each column, and then enter the correct column / field names.
JD SartainExcel divides column A into three columns.
7. Then you need to copy the first letter of the first name and middle name in column D. Enter the following formula in cell D2: = LEFT (A2,1) & LEFT (B2,1). Note that the result is MS for Matthew Steven.
8. Copy the formula from D2 to D3 to D13.
JD SartainExtract the first letters of the first and second names.
9. Now you need to calculate the number of letters of each last name. Copy this formula = LEN (C2) in cell E2, then copy it from E2 to E3 through E13.
10. Then enter the URL of the company preceded by the @ sign in column F; i.e. @ radius.com. You can just enter this URL once in F2 and then refer to this cell absolutely, which would look like this: = CONCAT (D2, C2,70, E2, $ F $ 2). Both methods give the same result, but the second method uses less memory.
11. Copy this formula in G2 up to G3 to G13 and that’s it! You now have new email addresses for everyone in your company.
JD SartainCalculate number of letters in last name + CONCATENATE email addresses
Note: Considerable time savings occur when you have 500 to 5,000 employees. With the above process, suddenly a week (or more) of manual labor can now be accomplished in less than half an hour.
Project 2: Extract domain names from URLs
People who work with the Internet and websites have an endless database to manage. When your boss hands you a file (regardless of the source) with thousands of URLs and says “manage this data,” the first job is to extract the domain names from the URLs.
Your job: sort the data first, use Find and Replace to remove the http: // (HyperText Transfer Protocol) and www (World Wide Web) prefixes and period punctuation.
1. Select Data> Sort > sort by: Column A (and choose ascending).
JD SartainFirst sort the URLs in ascending order.
2. Then select Home > Search and select > To replace (or just press Ctrl + H) and type: http: // www. (don’t forget the period at the end) in the Find field, then leave it To replace empty field. Click on Replace all.
3. Repeat this step (above) but this time type: https: // www.
JD SartainUse Find and Replace to remove URL prefixes.
4. The rest can be cleaned up with a simple formula: = LEFT (A2, FIND (“/“, A2 & ”/“) – 1)
5. Enter this formula in cell B2; then copy from B2 to B3 via B13.
JD SartainUse the LEFT + FIND functions to extract the rest.
Bonus tip: CONCATENATE tip
In Project 1 above, we used the Text to Columns wizard to separate the full names (e.g. Lisa Valerie Kudrow) from a column into three columns. Next, we used several functions including the CONCAT / CONCATENATE function to create new e-mail addresses for the 5,000 employees of Radius.com.
Here is a quick function using the ampersand to merge, i.e. CONCATENATE, first name, middle name and last name into one.
1. Enter this formula in cell D2: = A2 & “” & B2 & “” & C2. The “” (quotation marks-space-quotation marks) between the ampersands and cells A2, B2, and C2 tell Excel to add spaces between first name, middle name, and last name.
2. Copy this formula in D2 to D3 to D13 and that’s it! First name, middle name and last name are all merged into one.
JD SartainUse the ampersand instead of the CONCATENATE function.
