Pull First Name and last Name from email id
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
Answers
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!