Skip to main content
Solved

TRIM Formula

  • January 18, 2023
  • 3 replies
  • 67 views
C
Forum|alt.badge.img+2

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")

Best answer by courtneydean

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")

    3 replies

    Ben_Young1
    Forum|alt.badge.img+22
    • Ben_Young1
    • Brainy
    • January 20, 2023

    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:

     

    Ben_Young1
    Forum|alt.badge.img+22
    • Ben_Young1
    • Brainy
    • January 20, 2023

    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.

    C
    Forum|alt.badge.img+2
    • courtneydean
    • Author
    • New Participant
    • Answer
    • January 24, 2023

    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")
    Terms & ConditionsAccessibility statement