Pull First Name and last Name from email id

Gaurav Chauhan

Hi team, All our comp email id follow the same format as firstname.lastname@amplity.com

So, can you please confirm the formulae on how gaurav.chauhan@amplity.com would convert to Gaurav Chauhan

Also, would be advisable if the first letters can be uppercased as well

Jeff Reisman

@Gaurav Chauhan

I would use a help column "NameHelper" just to keep things from getting too complicated in one formula.

To isolate the name portion of the Email address, you need to use the FIND function to locate the position of the @ symbol:

=FIND("@", EmailId@row)

Then you use the LEFT function to pull all the characters leading up to the @:

=LEFT(EmailId@row, (FIND("@", EmailId@row) - 1))

Then you use SUBSTITUTE to change the "." to a blank space " ".

=SUBSTITUTE(LEFT(EmailId@row, (FIND("@", EmailId@row) -1), ".", " ")

So now your NameHelper column equals "gaurav chauhan".

In your Name column, we'll finalize the name in proper case using the FIND, UPPER, LOWER, LEFT, and MID functions:

First we collect the first character of the name and convert it to upper case:

=UPPER(LEFT(NameHelper@row, 1))

This gives us "G" to start with.

Next we find the position of the blank space:

=FIND(" ", NameHelper@row)

Next we'll use that position to collect the rest of the first name, by telling the system to start at the 2nd character and collect the number of characters equal to the position of the space minus 2.

=LOWER(MID(NameHelper@row, 2, (FIND(" ", NameHelper@row) -2)))

This gives us "aurav".

Next we're going to use the position of the space to collect the first letter of the last name in upper case by looking for the MID of the value starting one position after the space and collecting one character:

=UPPER(MID(NameHelper@row, (FIND(" ", NameHelper@row) + 1), 1))

This gives us "C".

Next we collect the rest of the last name in lower case, using MID and telling it to collect 40 characters; it will only collect the text that's present though!

=LOWER(MID(NameHelper@row, (FIND(" ", NameHelper@row) + 2), 40))

this gives us "hauhan".

Lastly, we add them all together, not forgetting the space!

=UPPER(LEFT(NameHelper@row, 1)) + LOWER(MID(NameHelper@row, 2, (FIND(" ", NameHelper@row) -2))) + " " + UPPER(MID(NameHelper@row, (FIND(" ", NameHelper@row) + 1), 1)) + LOWER(MID(NameHelper@row, (FIND(" ", NameHelper@row) + 2), 40))

Just make sure all the parentheses are color-coded correctly for each sub-formula.