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 | 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 | 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] (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 | 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:
| 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
