Help

Extract Firstname and Lastname from Email and Format it to Titlecase in 1 single Formula

Topic Labels: Formulas
Solved
Jump to Solution
1584 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Paul_Robinson
4 - Data Explorer
4 - Data Explorer

This is not a question more so a time saving work around for anyone else that faced this issue.

Scenario:

We have a column of emails in the format of ‘firstname.lastname@domain.com’
We needed to break this down to their full name in one column but the standard UPPER and LOWER case formulas left the data looking ugly and wasnt great for merging into emails.

Alas, this ugly AF formula is what I could piece together from various wild google chases.

Enjoy I hope it helps you.

REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(LOWER(UPPER(SUBSTITUTE(LEFT({Email address},FIND("@",{Email address})-1),"."," "))), "^a", "A"), "^b", "B"), "^c", "C"), "^d", "D"), "^e", "E"), "^f", "F"), "^g", "G"), "^h", "H"), "^i", "I"), "^j", "J"), "^k", "K"), "^l", "L"), "^m", "M"), "^n", "N"), "^o", "O"), "^p", "P"), "^q", "Q"), "^r", "R"), "^s", "S"), "^t", "T"), "^u", "U"), "^v", "V"), "^w", "W"), "^x", "X"), "^y", "Y"), "^z", "Z"), " a", " A"), " b", " B"), " c", " C"), " d", " D"), " e", " E"), " f", " F"), " g", " G"), " h", " H"), " i", " I"), " j", " J"), " k", " K"), " l", " L"), " m", " M"), " n", " N"), " o", " O"), " p", " P"), " q", " Q"), " r", " R"), " s", " S"), " t", " T"), " u", " U"), " v", " V"), " w", " W"), " x", " X"), " y", " Y"), " z", " Z")

If the email is empty this will return an error, I actually split the email off to a ‘username’ entry first and then cleaned the username rather.

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, @Paul_Robinson! :grinning_face_with_big_eyes: That formula looks very similar to one that I worked on for another user a while back. I felt like something shorter and more attuned to this specific use case was probably possible, so I played a bit and came up with this:

IF(Email, UPPER(LEFT(Email, 1)) & REGEX_EXTRACT(Email, "(?:.)([^\\.]*)") & " " & UPPER(MID(Email, FIND(".", Email) + 1, 1)) & REGEX_EXTRACT(Email, "(?:[^\\.]*..)([^@]*)"))

Screen Shot 2021-09-30 at 9.21.08 PM

See Solution in Thread

3 Replies 3
Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, @Paul_Robinson! :grinning_face_with_big_eyes: That formula looks very similar to one that I worked on for another user a while back. I felt like something shorter and more attuned to this specific use case was probably possible, so I played a bit and came up with this:

IF(Email, UPPER(LEFT(Email, 1)) & REGEX_EXTRACT(Email, "(?:.)([^\\.]*)") & " " & UPPER(MID(Email, FIND(".", Email) + 1, 1)) & REGEX_EXTRACT(Email, "(?:[^\\.]*..)([^@]*)"))

Screen Shot 2021-09-30 at 9.21.08 PM

Wow, thanks Justin that’s worked a treat !

Some small improvements for edge cases:

  • Using LOWER() in each REGEX_EXTRACT() in case capitals exist already elsewhere in the email
  • Accounting for emails with hyphens/dashes (e.g. susie-jo.adams@gmail…)

IF(NOT(FIND('-',Email)),UPPER(LEFT(Email, 1)) & REGEX_EXTRACT(LOWER(Email), "(?:.)([^\\.]*)") & " " & UPPER(MID(Email, FIND(".", Email) + 1, 1)) & REGEX_EXTRACT(LOWER(Email), "(?:[^\\.]*..)([^@]*)"),IF(AND(FIND('-',Email),FIND('-',Email)<FIND('.',Email)), UPPER(LEFT(Email, 1)) & REGEX_EXTRACT(LOWER(Email), "(?:.)([^\\-]*)") & '-' & UPPER(MID(Email, FIND("-", Email) + 1, 1)) & REGEX_EXTRACT(LOWER(Email), "(?:[^\\-]*..)([^.]*)") & ' ' & UPPER(MID(Email, FIND(".", Email) + 1, 1)) & REGEX_EXTRACT(LOWER(Email), "(?:[^\\.]*..)([^@]*)"),UPPER(LEFT(Email, 1)) & REGEX_EXTRACT(LOWER(Email), "(?:.)([^\\.]*)") & " " & UPPER(MID(Email, FIND(".", Email) + 1, 1)) & REGEX_EXTRACT(LOWER(Email), "(?:[^\\.]*..)([^-]*)") & '-' & UPPER(MID(Email, FIND("-", Email) + 1, 1)) & REGEX_EXTRACT(LOWER(Email), "(?:[^\\-]*..)([^@]*)")))