Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 30, 2021 08:58 PM
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.
Solved! Go to Solution.
Sep 30, 2021 09:25 PM
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, "(?:[^\\.]*..)([^@]*)"))
Sep 30, 2021 09:25 PM
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, "(?:[^\\.]*..)([^@]*)"))
Oct 01, 2021 03:35 AM
Wow, thanks Justin that’s worked a treat !
Oct 02, 2021 09:17 AM
Some small improvements for edge cases:
LOWER()
in each REGEX_EXTRACT()
in case capitals exist already elsewhere in the emailIF(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), "(?:[^\\-]*..)([^@]*)")))