A classical scenario to understand the excellence of Excel. There might be situations where we would like to split the email address in two parts i.e. the username (part at the left of @ symbol) and the domain name (part at the right of @ symbol).
For example firstname.lastname@example.org has the username hasaanfazalpk and gmail.com is the domain name. So consider a situation that you have thousands of such email addresses and you want to extract the username and domain name of each. This is where Excel shines again.
Method 1: Using Text to column tool
Step 1: Select the range from which you would like to extract or separate usernames/domain names
Step 2: Click Data tab in the Excel ribbon and hit text to column button in Data tools group
Step 3: Make sure the delimited option is selected and hit Next button
Step 4: Make sure only Other box is checked under delimiters options and in the box punch in @ symbol and hit Next button
Step 5: In the last step select the cell where you would like Excel to produce the results. In our example it is: $B$2. Hit Finish button.
It is the simplest possible method to get this sort of job done and it is pretty fast too.
Following animation walks you through the whole process:
Method 2: Using Excel functions
We can get this job done using Excel functions as well and there are lots of different ways in which we can devise a formula to get it done. The difference in this technique beside the use of formula is that you have select the range where you want the output in the start of the process as oppose to the Method 1 where we selected it as the last step.
The other notable difference is the Text to Column is more of a split approach whereas using formula it is more of an extract approach. In simple words TtC splits the email address in two in one stroke whereas with formulas we extract a specific portion.
Step 1: With the email addresses in column A, to extract username type this formula in cell B1 and hit Enter key:
Following animation shows the result of this formaula:
Extracting Domain name
Now that we have understood how to get the username, it will be easy for us to understand the mechanics for extracting domain name.
With cell A1 being the one with the email address in cell C1 put this formula to extract the domain name:
Hit Enter key on the keyboard and drag the fill handle to get the domain names from other email addresses to in the range.
Here is the illustration for you to see it in action:
The above formula has two main changes:
- Instead of LEFT now are using RIGHT function so that Excel start counting from the right of text string.
- LEN is the additional function which counts the total number of characters in the text string.
Now I will leave you to your common sense and a hint of imagination to get your neurons working to understand how the formula works 🙂