Help

TRIM Formula

Solved
Jump to Solution
856 3
cancel
Showing results for 
Search instead for 
Did you mean: 
courtneydean
4 - Data Explorer
4 - Data Explorer

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

Accepted Solutions
courtneydean
4 - Data Explorer
4 - Data Explorer

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

See Solution in Thread

3 Replies 3

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_0-1674238661893.png

 

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:

Ben_Young1_0-1674239219922.png

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

courtneydean
4 - Data Explorer
4 - Data Explorer

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