Friday Challenge – Splitting Up Email Address Text with Excel Formulas

Alright, this challenge was posted by a fan in on my Youtube Channel.  It was posted for this video: “Splitting Cell Text and Numbers Using Formulas And Text to Columns“ and blog post “How-to Concatenate Excel Column or Row Data Using Google Docs

Tracey Shaw wrote:

I would like to see a demo to an split email address across columns. For example, I have one column with [email protected]. I need to see Jane in one column, M in another column and Doe in another column.

See if you can answer the original question, the extended question and the extra credit question.  But like any quiz, there are rules.  These rules apply to all questions in this Excel Test.

RULE 1: the formula must work for any size of first, middle, last or domain names.





RULE 2: You can use formulas from Excel 2007, Excel 2010 and Excel 2013)

RULE 3: You can NOT use helper formulas.  All formulas must be contained in one cell per column of data

1) Original Question: Create one formula in each of cells B2:E2 that will break up the following email text into the 4 components (First Name, Middle Name, Last Name and Email Domain) for [email protected]    Answers should be in cells B2:E2

Your output should look like this:

A B C D E
1 Email First Middle Last Domain
2 [email protected] jane m doe xxx.co

Then you should also test your formulas against these other emails:



A B C D E
1 Email First Middle Last Domain
2 [email protected] jane m doe xxx.co
3 [email protected] J R Ewing dallas.com
4 [email protected] John At h on.be

 

2) Bonus Question: Change your formulas in question one for these two additional emails.  Answers should be in cells B2:E5

[email protected]

Want to Receive the Next Post?
Join My Newsletter
Subscribe
Give it a try, you can unsubscribe anytime.
Privacy Policy

[email protected] (assume first.last before the @ symbol)

[email protected] (assume first name before the @ symbol)





Your output should look like this:

A B C D E
1 Email First Middle Last Domain
2 [email protected] jane m doe xxx.co
3 [email protected] J R Ewing dallas.com
4 [email protected] John At h on.be
5 [email protected] john doe xxx.com
6 [email protected] mary xxx.com

 

3) Extra Credit Question: See who can write the smallest 4 formulas for [email protected]  (The smallest will be calculated by adding up all the characters in the 4 formulas in cells B2:E2)

Here is my character counts including the equals sign:





If you found the website and tutorials helpful, please consider donating to keep the lights on.

Donate with PayPal here:





A B
7 Position Characters in Formula
8 First 24
9 Middle 66
10 Last 81
11 Domain 31

 

4) Double Bonus Extra Credit Question: See if you can write the 4 formulas without using the FIND function for [email protected]



…..hmmm….I don’t have an answer for this one, but I am hoping one of the Excel fans out there can figure one out.  It may also be the winner of quiz item 3 🙂

 

Okay, post your answers and formulas below in the comments section.  I have a moderated comments section, so you will not see your comment post directly until I approve it. (gotta stop the spammers).  Also, I will not release the comments until Monday since this is a closed book test :).  Also, let me know if you have any questions in the comments below.  Good luck!

Steve=True









Want to Receive the Next Post?
Join My Newsletter
Subscribe
Give it a try, you can unsubscribe anytime.
Privacy Policy

LEAVE A REPLY

Please enter your comment!
Please enter your name here