In my previous Friday Challenge, Tracey wanted to know how to split up email address text in Excel. The sample text that Tracey wanted to break up was in this format:
Now in my last post, I showed you what I think is the absolutely fastest way to extract the first, last and middle names of an email address. You can check out that step-by-step tutorial and video here:
Friday Challenge Answer Part 1: Splitting Up Email Address Text
But lets see how we can do this with formulas. Now one caveat, this technique will show you how to break up this specific email address text. Your email text may be in a slightly different format, but you can tweak the formulas as needed. For instance, what if your email is [email protected]? You can use the same formula, but change the reference to a dash “-“ instead of the “.” period. But this won’t and can’t work if your emails are in this format with no delineation “[email protected]”.
In this post, we will do the following
1) Investigate how to make a formula to cut out the first, middle, last and domain names of the “[email protected]” email format as per the original question.
2) By doing this we will complete parts 1 and 3 of the Challenge and scroll down to find out what other solutions were submitted.
In my next post, we will review parts 2 and 4 of the challenge. You can check out all the challenge questions and still submit your answers at this URL:
Friday Challenge – Splitting Up Email Address Text with Excel Formulas
Okay, back to the original question
Now this may seem on the surface a very simple formula, but once you get into it, the formula can get very complex. Lets check it out, but you can see a full video at the bottom of the post.
The Breakdown
Use a combination of the the FIND, LEFT, RIGHT, LEN and MID functions to create 4 formulas.
1) Formula for the Email Address First Name
2) Formula for the Email Address Middle Name
3) Formula for the Email Address Last Name
4) Formula for the Email Address Domain Name
Step-by-Step
Okay, lets assume that your data is like this:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | First | Middle | Last | Domain | |
| 2 | [email protected] | ||||
| 3 | [email protected] | ||||
| 4 | [email protected] |
1) Formula for the Email Address First Name
Looking at the email address, the first name is the first part of the email that left of the first period. The LEFT function has 2 parts to it. One is what text and the second is how many characters.
So we start with this formula in cell B2:
=Left(A2, (A2 is where our email is located)
Now the second part of the left function is how many characters should the Left function return? Well we need to count where the first period is in the email address. You can do this with a Find Function.
=Left(A2,Find(“.”,A2)
Now this will return the text of “jane.” which is one character too long, so we need to subtract one from the FIND function to get just the first name.
Here is your final formula:
=LEFT(A2,FIND(“.”,A2)-1) (With an email in cell A2, copy and paste this into cell B2 to split out the first name from the email address)
2) Formula for the Email Address Middle Name
This is the second toughest formula to write. But if we apply some logic and reason to what we need to find, we can do this. Since the middle name is in between the first period and the second period, we can use this to our advantage, but it is tricky. Since our middle name is not on the left or the right, we need to use the MID Function in Excel.
Now there are 3 parts to the MID spreadsheet function in Excel.
One is the Mid of what text. In our case, that is the email address in cell A2
So in Cell C2, type in this formula:
=MID(A2,
The second part of the MID function where do you want to start grabbing text within the Email address?
We are going to do this with the FIND function once again by finding the first period. Now since we want to grab text that is one letter past the first period, we need to add a +1 to this Find. So enter in the find in your MID function:
=MID(A2,FIND(“.”,A2)+1,
The third part of the MID function how many characters do you want to return from this mid point?
This is the Excel trick of this formula. We have to somehow calculate the number of characters between the first and second periods. To do this, we will once again use the FIND Function. So lets dissect the FIND function.
There are three parts to the FIND Function.
One: Find what text?
Two: Within what text”?
Three: What character number do you want to start looking for this text?
Most people stop at the first two arguments of the FIND function because that is all they need, but we need to use the third function start our counter at the first period position. So in essence, we are going to find the position of the second period starting from just past the position of the first period. To do that, we need to nest another FIND function within a FIND function as the third argument. And since that would have us starting at the period, we need to start +1 character past the first period. So lets enter that into our formula:
=MID(A2,FIND(“.”,A2)+1,FIND(“.”,A2,FIND(“.”,A2)+1)
Okay, so we have found half of the third part of the MID function (Remember that it is = how many characters do you want to return from this mid point?) Now since we have the location of the second we just need to subtract the location of the first period. So lets find the first period and subtract that amount and also one more character so that we don’t show the period. Your final formula will look like this in cell C2:
=MID(A2,FIND(“.”,A2)+1,FIND(“.”,A2,FIND(“.”,A2)+1)-FIND(“.”,A2)-1)
You now have a way to get any middle name from between two periods from any email address.
3) Formula for the Email Address Last Name
Alright, this is the toughest one of all the formulas so far because we need to keep jumping from one period location to another within the email address. Once again we are going to use the MID Function. And remember that there are 3 parts to the MID spreadsheet function in Excel.
One is the Mid of what text. In our case, that is the email address in cell A2 so type this in cell D2 as we are going to use A2 as our text to look in:
=MID(A2,
The second part of the MID function where do you want to start grabbing text within the Email address?
We are going to do this with the FIND function once again by finding the second period. Now since we want to grab text that is one letter past the second period, we need to add a +1 to this Find. In order to find the second period, we need to nest another FIND function in the first FIND function. We always add a +1 in each of the finds so that we jump past the period for the last name location. Essentially, we are finding the first period, then advancing one character and then finding the next period from that point. So enter in the find in your MID function:
=MID(A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1,
The third part of the MID function how many characters do you want to return from this mid point?
We must calculate the number of characters between the second period and the “@” at symbol to get the last name from the email address. We do this by finding the location in characters of the “@” at symbol and subtracting the location in characters of the second “.” period. You will have to nest two FIND functions like you did before. We have added a minus one so that we do not include the “@” symbol in our last name text. Your final formula for the Last Name of your email address is as follows:
=MID(A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1,FIND(“@”,A2)-FIND(“.”,A2,FIND(“.”,A2)+1)-1)
4) Formula for the Email Address Domain Name
To find the domain name in the email address of [email protected], it is everything right of the “@” symbol. So to get this value, we will use the RIGHT function.
Like the LEFT function, there are 2 parts to the RIGHT function. One is what text and the second is how many characters. So in cell E2, type in the first part of our formula:
=Right(A2, (A2 is the where text for our formula)
Since we are using the right function, we have to work a little differently. We first need to find out how long is the text string (email address). Then we need to find the “@” symbol position. If we subtract these two values, we can tell the right function what it needs to know.
To do this, we first use the LEN Function to get the length in characters of the email address. So type in the next part:
=RIGHT(A2,LEN(A2)
Now we need to subtract the position in characters of the “@” symbol. To do this, we just need to use the FIND Function. Since we are subtracting this value from the overall length, type in the minus symbol and then type in your Find function:
=RIGHT(A2,LEN(A2)-FIND(“@”,A2))
Now you have found the domain of your email address using a formula in Excel. I still prefer splitting the email addresses with Text to Columns function. Sometimes the easiest way is the best way. But who doesn’t like figuring out a complex formula that works! 
Challenge Answers
Okay, so now you see my formulas for the original question above. How did you do?
I got an awesome response from Don. He matched the First Name and Domain Name split out perfectly. However, he didn’t use FIND, he used the SUBSTITUTE Function but that meant that his character count was higher than mine. It looks like Don is using the Substitute function in Excel to change the second period to a “.2” so that you know where it is within the string. How awesome is that? However, this may have an unexpected consequence if there are numbers that are entered into the email text string around the periods. Not highly likely, but it could happen…maybe . Not sure, maybe this is too remote a situation to really worry about. GREAT JOB DON!
| A | B | C | D | |
|---|---|---|---|---|
| 20 | Mine | Don’s | ||
| 21 | First | 24 | 24 | =LEFT(A2,FIND(“.”,A2)-1) |
| 22 | Middle | 66 | 77 | =MID(A2,FIND(“.”,A2)+1,FIND(“.”&2,SUBSTITUTE(A2,”.”,”.”&2,2))-FIND(“.”,A2)-1) |
| 23 | Last | 81 | 103 | =MID(A2,FIND(“.”&2,SUBSTITUTE(A2,”.”,”.”&2,2))+1,FIND(“@”,A2)-FIND(“.”&2,SUBSTITUTE(A2,”.”,”.”&2,2))-1) |
| 24 | Domain | 31 | 31 | =RIGHT(A2,LEN(A2)-FIND(“@”,A2)) |
Video Tutorial
Free Excel Dashboard Template File:
You can find the download file in Part 3 of this tutorial as the final formulas will take into account other email address formats like:
[email protected] (First only)
[email protected] (No Milddle Name)
Thanks for being fans and submitting your answers. There is not usually a right answer, just different ways to go about tackling the problem. I learn so much from your answers, so keep them coming. Also, keep the questions coming and we will get to them as fast as we can. Also, don’t forget to subscribe so you get the next post delivered right to your inbox.
Steve=True
