Create custom email addresses & extract domain names from URLs - lathamhade1972

Text to Columns Thaumaturge: Make unique email addresses

In an effort to control future emails, a monumental hardware firm enclosed email forms happening its website, so drug user emails would be directed to a centralized location. Regrettably, the arrange 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, victimisation this common format, figured out how to email individuals directly. This resulted in mass chaos.

Your job: Assign netmail addresses that make over sense to the employees, but not to the general public. Aft much deliberation, the new format is: User first and middle initials, asset substance abuser last key, plus the number of characters in the last name preceded by 700. So, Lisa Valerie Kudrow would comprise LVKudrow706@radius.com. Notation that email addresses are NOT cause sensitive.

Use the School tex to Columns Genius

1. Enter 10 or 15 names in column A (as if the name calling came from an ASCII or CSV text data file). In this case, the names are in cells A2 through A13. Highlight that range.

2. Next, separate the names into three separate columns. Select Information > Text to Columns.

3. In the Convert Text to Columns Wizard dialogue boxful, ensure that the Finite push is checked/ticked, and so fall into place Next.

JD Sartain

Convert Text to Columns Sensation, quality Delimited.

4. In step 3, to a higher place, notice the options for Delimiters: Tab, Semicolon, Comma, Distance, Other. Check the Space box—card how Stand out divides the columns in the preview window below—then click Close.

JD Sartain

For the Delimiters, check the Space box option.

5. In Step 4 above, notice the options for Column Data Data format: Ecumenical, Textual matter, Date, Coiffure Not Significance Column (Skim). Select the Text button. Click the incoming column and select Text again, and the last editorial and Text again, etc.. Finally, click the Finish release.

JD Sartain

Select the Text button for all three William Claude Dukenfield.

6. Notice that Stand out splits column A into three columns: A, B, &adenylic acid; C. Adjust the column widths to outfit the text in each column, then enter the correct editorial/playing area names.

JD Sartain

Excel splits column A into three columns.

7. Next, you require to copy the first letter of the early and heart names into column D. Enter the undermentioned formula in cell D2: =LEFT(A2,1)&Liberal(B2,1). Notice the consequence is MS for Matthew Steven.

8. Copy the formula from D2 to D3 through and through D13.

JD Sartain

Extract the first letters of the first-class honours degree and middle names.

9. Now you need to calculate the number of letters in each last name. Replicate this formula =LEN(C2) in cell E2, then copy information technology down from E2 to E3 through E13.

10. Next, enter the company URL preceded past the @ sign in chromatography column F; that is, @radius.com. You could just enter this URL one time in F2, so make the credit to that cell absolute, which would aspect like this: =CONCAT(D2,C2,70,E2,$F$2). Both ways yield the same result, but the second method acting uses less memory.

11. Copy this formula in G2 down to G3 through G13 and that's information technology! Now you have new email addresses for every your company's employees.

JD Sartain

Calculate the numerate of letters in Last Name + CONCATENATE the email addresses

Line: The massive time savings comes when you have 500 to 5000 employees. With the process above, suddenly, a workweek's deserving (or more) of manual run can now exist completed in less than a one-half-hour.

Project 2: Extract domain name calling from URLs

People who work with the Internet and websites have an endless database to manage. When your boss workforce you a file (heedless of the source) with thousands of URLs and says "superintend this data," the first task is to extract the domain names from the URLs.

Your job: Sort the information first, use Hunt and Replace to eliminate the http:// (HyperText Transfer Communications protocol) and www (web) prefix plus the period punctuation mark.

1. Select Data > Sort > Sort By: Column A (and choose ascending).

JD Sartain

First sort the URLs in ascending order.

2. Next, select Base > Find & Choose > Replace (or just press Ctrl+H) and eccentric: http://www. (put on't draw a blank the period at the end) in theFind field, then depart the Supervene upon landing field blank. Click Replace Completely.

3. Repeat this step (preceding) but, this metre, type: https://www.

JD Sartain

Economic consumption Search & Replace to take out the URL prefixes.

4. The rest can live cleaned heavenward with one simple formula: =Leftish(A2, Detect("/", A2&A;"/")-1)

5. Enter this formula in cell B2; so copy from B2 down to B3 through B13.

JD Sartain

Manipulation the LEFT + Regain functions to extract the rest.

Bonus tip: CONCATENATE trick

In Project 1 above, we used the Text to Columns Wizard to reprint the full name calling (e.g., Lisa Valerie Kudrow) from one column into trey columns. Then we used several functions including the CONCAT / CONCATENATE function to create sunrise email addresses for the 5000 employees at Radius.com.

Here's a quick function using the ampersand to mix–that is, CONCATENATE–the first, eye, and last names indorse into one.

1. Enter this formula in cell D2: =A2&" "&adenosine monophosphate;B2&" "&C2. The " " (quotes-space-quotes) 'tween the ampersands and cells A2, B2, and C2 tell Excel to add spaces betwixt the first, middle, and last name calling.

2. Simulate this formula in D2 down to D3 through D13 and that's it! The forward, middle, and last name calling are al remerged into one.

JD Sartain

Use the ampersand in place of the CONCATENATE function.

Source: https://www.pcworld.com/article/411775/create-custom-email-addresses-and-extract-domain-names-from-urls.html

Posted by: lathamhade1972.blogspot.com

Share this post