Skip to main content

I have a formula field that creates an email address for me, but when there's an extra space after someone's last name it returns an extra "." and breaks my automation connection to create the email. 

Ex. sandy.castellano.  --- it should be sandy.castellano 

I thought adding TRIM to the last name would fix the issue but I keep getting an error code. Can anyone help with removing extra spaces in the last name? 

Current Code: 


SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
  (REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(LOWER(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(first_name & " " & last_name, ",", " "), "'", ""), "  ", " ")), " ", "."), "\\.\\.", "."), '-', '.')),
"á", "a"),
"à", "a"),
"â", "a"),
"Ã¥", "a"),
"æ", "ae"),
"ç", "c"),
"è", "e"),
"é", "e"),
"ë", "e"),
"ê", "e"),
"î", "i"),
"ï", "i"),
"ì", "i"),
"ñ", "n"),
"ô", "o"),
"ö", "o"),
"ò", "o"),
"ø", "o"),
"õ", "o"),
"ù", "u"),
"û", "u"),
"ü", "u"),
"ý", "y"),
"í", "i"), 
"ú", "u"),
"ÅŸ", "s"),
"ó", "o")

Hey @courtneydean!
Give this formula a shot:

IF(
AND(
{first_name},
{last_name}
),
IF(
REGEX_MATCH(
TRIM({first_name}) & TRIM({last_name}),
"[á,à,â,å,æ,ç,è,é,ë,ê,î,ï,ì,ñ,ô,ö,ò,ø,õ,ù,û,ü,ý,í,ú,ş,ó]"
),
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(
TRIM(LOWER({first_name})) & "." & TRIM(LOWER({last_name})),
"ó", "o"
),
"ÅŸ", "s"
),
"ú", "u"
),
"í", "i"
),
"ý", "y"
),
" ù,û,ü]", "u"
),
" ô,ö,ò,ø,õ]", "o"
),
"ñ", "n"
),
" î,ï,ì]", "i"
),
" è,é,ë,ê]", "e"
),
"ç", "c"
),
"æ", "ae"
),
"Ãá,à,â,Ã¥]", "a"
),
TRIM(LOWER({first_name} & "." & {last_name}))
)
)

Here's what the formula looks like in Airtable:

 

Hey @courtneydean! 

For some reason, every time I try to post a reply with the formula body text that I wrote, the post kinda just disappears.

I've just tossed the formula I created into a gist that can be accessed here.
Here's a look at what the formula looks like in Airtable:

I'm happy to answer any questions you might have about the formula or help with any additional adjustments if you're curious.

I was able to figure it out with the help of Collen w/ Airtable Support!

Had to convert the look up field to a text string. 

 

SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE((REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(LOWER(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(first_name & " " & TRIM(ARRAYJOIN(last_name)), ",", " "), "'", ""), " ", " ")), " ", "."), "\\.\\.", "."), '-', '.')),
"á", "a"),
"à", "a"),
"â", "a"),
"Ã¥", "a"),
"æ", "ae"),
"ç", "c"),
"è", "e"),
"é", "e"),
"ë", "e"),
"ê", "e"),
"î", "i"),
"ï", "i"),
"ì", "i"),
"ñ", "n"),
"ô", "o"),
"ö", "o"),
"ò", "o"),
"ø", "o"),
"õ", "o"),
"ù", "u"),
"û", "u"),
"ü", "u"),
"ý", "y"),
"í", "i"), 
"ú", "u"),
"ÅŸ", "s"),
"ó", "o")

Reply


Terms & Conditions