Skip to main content

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.

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, "(?:[^\\.]*..)([^@]*)"))


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, "(?:[^\\.]*..)([^@]*)"))



Wow, thanks Justin that’s worked a treat !

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, "(?:[^\\.]*..)([^@]*)"))



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), "(?:?^\\-]*..)()^@]*)")))

Reply


Terms & Conditions