Extract, first, last, and email address

Ted Penner
Ted Penner used Ask the Experts™
on
This version of the expression extracts the email address from a string of characters contained within cell E2 and replaces those characters with the result.

I also need versions of the expression that will extract the first name and one to extract the last name.

The email address can possibly be defined as any string of characters that are separated by an @ symbol with no spaces and surrounded entirely by either a (parenthesis) or <chevrons>.

This should work in a google sheet such as this one here https://docs.google.com/spreadsheets/d/1rQ5QC6Ipr5kkBDuNnIMY05Z09Q4q7lqZ52vd0bfCvtY/edit#gid=1895941459

=Regexextract(E2,"[A-z0-9._%+-]+@[A-z0-9.-]+\.[A-z]{2,4}")

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ted PennerSoftware Engineer

Author

Commented:
It would be better if we could gscript the solution.
Test your restores, not your backups...
Top Expert 2016
Commented:
I would suggest you provide more comprehensive test data / description as well to save time / iterations.

Things that come to mind:
  1. Will there always be exactly two names separated by a space?
  2. Could there be suffixes, prefixes, or middle names / initials to deal with?
  3. Could there be an email address with no name in front of it?
  4. . . .


»bp
Ted PennerSoftware Engineer

Author

Commented:
I agree, just have to test over time.

So far, this is what I have.

=SPLIT(REGEXREPLACE(REGEXEXTRACT(E4,".*<|.*\("), "<|\(", ""), " ")

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial