Pull a first and last name from an auto email cell

Taylor Riley · 07/23/20

I'm looking to pull a first and last name from an auto populated email address from a form. I want to auto populate a "Requestor Name" column based on the email that was generated.

For example:

Email - Joe_Smith@gmailcom

Requestor Name - Joe Smith

Paul Newcome · 07/23/20

Will it always be "first_last"?

Taylor Riley · 07/23/20

There may be a number after the last name, but for the most part it is always first_last

Paul Newcome · 07/23/20

I assume you also want to exclude that number?

Taylor Riley · 07/23/20

If possible, that would be great!

Paul Newcome · 07/23/20

Ok. Let me do some testing, and I will get back to you with what I find. I am sure it is possible though.

Paul Newcome · 07/23/20

So the most straightforward (but not very pretty) way of doing this would be to nest a series of SUBSTITUTE functions to replace the underscore with a space and any numbers with empty after using a LEFT/FIND to pull the left part of the string before the @.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(Email@row, FIND("@", Email@row) - 1), "_", " "), "0", ""), 1, ""), "2", ""), "3", ""), "4", ""), "5", ""), "6", ""), "7", ""), "8", ""), "9", "")

Taylor Riley · 07/23/20

That worked!

Thank you so much!

Paul Newcome · 07/23/20

Happy to help! 👍️

Pull a first and last name from an auto email cell

Answers

Help Article Resources

Categories