Create custom email addresses & extract domain names from URLs
Image: Rob Schultz
Text to Columns Wizard: Create unique email addresses
In an effort to control incoming emails, a massive hardware firm included email forms on its website, so user emails would be directed to a centralized location. Unfortunately, the format for its employees’ emails; that is first name, underscore, last name @company name.com—was common; therefore, too easy. So, customers Googled the corporate employee list and, using this common format, figured out how to email individuals directly. This resulted in mass chaos.
Your job: Assign email addresses that make sense to the employees, but not to the general public. After much deliberation, the new format is: User first and middle initials, plus user 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 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 that range.
2. Next, separate the names into three separate columns. Select Data > Text to Columns.
3. In the Convert Text to Columns Wizard dialog box, ensure that the Delimited button is checked/ticked, then click Next.
JD SartainConvert Text to Columns Wizard, select Delimited.
4. In step 3, above, notice the options for 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 the Delimiters, check the Space box option.
5. In Step 4 above, notice the options for Column Data Format: General, Text, Date, Do Not Import Column (Skip). Select the Text button. Click the next column and select Text again, and the last column and Text again, etc.. Finally, click the Finish button.
JD SartainSelect the Text button for all three fields.
6. Notice that Excel splits column A into three columns: A, B, & C. Adjust the column widths to fit the text in each column, then enter the correct column/field names.
JD SartainExcel splits column A into three columns.
7. Next, you need to copy the first letter of the first and middle names into column D. Enter the following formula in cell D2: =LEFT(A2,1)&LEFT(B2,1). Notice the result is MS for Matthew Steven.
8. Copy the formula from D2 to D3 through D13.
JD SartainExtract the first letters of the first and middle names.
9. Now you need to calculate the number of letters in each last name. Copy this formula =LEN(C2) in cell E2, then copy it down from E2 to E3 through E13.
10. Next, enter the company URL preceded by the @ sign in column F; that is, @radius.com. You could just enter this URL once in F2, then make the reference to that cell absolute, which would look like this: =CONCAT(D2,C2,70,E2,$F$2). Both ways yield the same result, but the second method uses less memory.
11. Copy this formula in G2 down to G3 through G13 and that’s it! Now you have new email addresses for all your company’s employees.
JD SartainCalculate the number of letters in Last Name + CONCATENATE the email addresses
Note: The massive time savings comes when you have 500 to 5000 employees. With the process above, suddenly, a week’s worth (or more) of manual work can now be completed in less than a half-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 task is to extract the domain names from the URLs.
Your job: Sort the data first, use Search and Replace to eliminate the http:// (HyperText Transfer Protocol) and www (world wide web) prefix plus the period punctuation.
1. Select Data > Sort > Sort By: Column A (and choose ascending).
JD SartainFirst sort the URLs in ascending order.
2. Next, select Home > Find & Select > Replace (or just press Ctrl+H) and type: http://www. (don’t forget the period at the end) in the Find field, then leave the Replace field blank. Click Replace All.
3. Repeat this step (above) but, this time, type: https://www.
JD SartainUse Search & Replace to remove the URL prefixes.
4. The rest can be cleaned up with one simple formula: =LEFT(A2, FIND(“/“, A2&”/“)-1)
5. Enter this formula in cell B2; then copy from B2 down to B3 through B13.
JD SartainUse the LEFT + FIND functions to extract the rest.
Bonus tip: CONCATENATE trick
In Project 1 above, we used the Text to Columns Wizard to separate the full names (e.g., Lisa Valerie Kudrow) from one column into three columns. Then we used several functions including the CONCAT / CONCATENATE function to create new email addresses for the 5000 employees at Radius.com.
Here’s a quick function using the ampersand to merge–that is, CONCATENATE–the first, middle, and last names back into one.
1. Enter this formula in cell D2: =A2&” “&B2&” “&C2. The “ “ (quotes-space-quotes) between the ampersands and cells A2, B2, and C2 tell Excel to add spaces between the first, middle, and last names.
2. Copy this formula in D2 down to D3 through D13 and that’s it! The first, middle, and last names are al remerged into one.
JD SartainUse the ampersand in place of the CONCATENATE function.