Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

TRIM Formula

Solved
Jump to Solution
135 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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.

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