Can you extract first name & last name from email address? [Formula Challenge]
Today lets rescue John Doe from John_doe@email.com.
Extract first & last name from email address
As you may know, we have an article on how to extract names from email addresses. 2 days ago, Joana commented on it saying,
Not to brag but I created a [complex] formula that extracts the names from emails in a much more interesting way. See the examples:
firstname.lastname@example.org >> my extract >> John Doe
email@example.com >> my extract >> John Doe
firstname.lastname@example.org >> my extract >> John Doe
I asked Joana how she did it. And here is the formula she shared (#),
Yes, it is long. It must have taken a lot of concentration, ninja-level skills to come up with this.
(Note: The formula is in Portuguese or Spanish version of Excel. So do not try it in English version)
Here is your challenge.
Given an email address in the format
You need to extract first name & last name using formulas.
Things to keep in mind:
- Assume only English alphabet in names. That means no letters like áèó etc.
- The email address contains only firstname_separator_lastname. No middle name or other prefix or suffix etc.
- The email address is in A1
- Assume B1 contains just the name portion of email (ie john_doe in B1 if A1 contains email@example.com)
- In C1 & D1 you need to extract first name & last name.
Example email addresses:
- firstname.lastname@example.org -> john doe
- email@example.com -> john doe
- firstname.lastname@example.org -> john doe
- email@example.com -> john doe
- firstname.lastname@example.org -> john doe
Download the sample file containing email addresses and expected results. Use it to write your formulas.
How to post your answers?
Simple. Just comment on this post with your answers. Tell us how you arrived at the formula, what it does. It will help rest of us understand and use your formulas.
Special note: If your formula contains < or > symbols when posting it, use < and > instead. Our commenting system eats up < and > symbols.
Go ahead and liberate John Doe from email@example.com. We are waiting…
Want more Excel challenges?
Try this – more than 25 challenges and problems in Excel.
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Robust Dynamic (Cascading) Dropdowns Without VBA||Handle Volatile Functions like they are dynamite »|