Help

Re: Formula to replace special characters - slug creation

12371 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Jay_Adam
4 - Data Explorer
4 - Data Explorer

We’re a film festival looking to pull the titles of our submissions in order to create slugs that can be used to create virtual posts on our WordPress site.

I need a way to parse just the text from the submission, with the ability to remove special characters (i.e. " ’ ! ? ; : @ # $ % ^ etc.).

I’ve gotten to this point so far:

LOWER(SUBSTITUTE({English Title}, " ", "-"))

This allows me to hyphenate the titles, but still includes special characters. Is anyone aware of a workaround for this, or do I need to manually create my slugs in this case?

Cheers!

27 Replies 27

For a long text field, you need to add another SUBSTITUTE layer that replaces \n with %0A

That worked - thanks so much Justin! :slightly_smiling_face:

Ambroise_Dhenai
8 - Airtable Astronomer
8 - Airtable Astronomer

There should be an easy-to-use function for this, really. It’s so common to generate slugs for url-related things.

Ambroise_Dhenai
8 - Airtable Astronomer
8 - Airtable Astronomer

@W_Vann_Hall FYI you should use ENCODE_URL_COMPONENT for url encoding, it’s different than slugs.

See https://support.airtable.com/hc/en-us/articles/203255215-Formula-field-reference

Ambroise_Dhenai
8 - Airtable Astronomer
8 - Airtable Astronomer

For those looking for a real SLUG function that covers most accents:

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(
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(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
  TRIM(LOWER(labelFR)),
"á", "a"),
"à", "a"),
"â", "a"),
"å", "a"),
"æ", "ae"),
"ç", "c"),
"è", "e"),
"é", "e"),
"ë", "e"),
"ê", "e"),
"î", "i"),
"ï", "i"),
"ì", "i"),
"ñ", "n"),
"ô", "o"),
"ö", "o"),
"ò", "o"),
"ø", "o"),
"õ", "o"),
"ù", "u"),
"û", "u"),
"ü", "u"),
"ý", "y"),
" ", "-"),
"!", "-"),
".", "-"),
"/", "-"),
"\"", "-"),
"#", "-"),
"$", "-"),
"%", "-"),
"&", "-"),
"'", "-"),
"(", ""),
")", ""),
"*", "-"),
"+", "-"),
",", "-"),
":", "-"),
";", "-"),
"<", "-"),
"=", "-"),
">", "-"),
"?", "-"),
"@", "-"),
"[", ""),
"\\", "-"),
"]", ""),
"^", "-"),
"_", "-"),
"`", "-"),
"{", ""),
"|", "-"),
"}", ""),
"~", ""),
"«", ""),
"»", ""),
"€", ""),
"„", ""),
"“", ""),
"-----", "-"),
"-----", "-"),
"----", "-"),
"---", "-"),
"--", "-")


Usage in a Rollup field:
image

values & "-" & 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(
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(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
  TRIM(LOWER(labelFR)),
"á", "a"),
"à", "a"),
"â", "a"),
"å", "a"),
"æ", "ae"),
"ç", "c"),
"è", "e"),
"é", "e"),
"ë", "e"),
"ê", "e"),
"î", "i"),
"ï", "i"),
"ì", "i"),
"ñ", "n"),
"ô", "o"),
"ö", "o"),
"ò", "o"),
"ø", "o"),
"õ", "o"),
"ù", "u"),
"û", "u"),
"ü", "u"),
"ý", "y"),
" ", "-"),
"!", "-"),
".", "-"),
"/", "-"),
"\"", "-"),
"#", "-"),
"$", "-"),
"%", "-"),
"&", "-"),
"'", "-"),
"(", ""),
")", ""),
"*", "-"),
"+", "-"),
",", "-"),
":", "-"),
";", "-"),
"<", "-"),
"=", "-"),
">", "-"),
"?", "-"),
"@", "-"),
"[", ""),
"\\", "-"),
"]", ""),
"^", "-"),
"_", "-"),
"`", "-"),
"{", ""),
"|", "-"),
"}", ""),
"~", ""),
"«", ""),
"»", ""),
"€", ""),
"„", ""),
"“", ""),
"-----", "-"),
"-----", "-"),
"----", "-"),
"---", "-"),
"--", "-")

Renders:
image

I’ve taken quite a good care to handle all ascii chars.

The formula posted by @W_Vann_Hall was made in September of 2018, before the ENCODE_URL_COMPONENT() function was added to Airtable.

That works well, thank you!

Carlos_Hurtado
5 - Automation Enthusiast
5 - Automation Enthusiast

I included a ton of extra characters! Hope this can help someone:

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( 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( 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( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( TRIM(LOWER(Replace With Your Column Name)), “à”, “a”), “á”, “a”), “â”, “a”), “ä”, “a”), “æ”, “a”), “ã”, “a”), “å”, “a”), “ā”, “a”), “b”, “b”), “ç”, “c”), “ć”, “c”), “č”, “c”), “d”, “d”), “è”, “e”), “é”, “e”), “ê”, “e”), “ë”, “e”), “ē”, “e”), “ė”, “e”), “ę”, “e”), “f”, “f”), “g”, “g”), “h”, “h”), “î”, “i”), “ï”, “i”), “í”, “i”), “ī”, “i”), “į”, “i”), “ì”, “i”), “j”, “j”), “k”, “k”), “l”, “l”), “ł”, “l”), “m”, “m”), “ñ”, “n”), “ń”, “n”), “ô”, “o”), “ö”, “o”), “ò”, “o”), “ó”, “o”), “œ”, “o”), “ø”, “o”), “ō”, “o”), “õ”, “o”), “p”, “p”), “q”, “q”), “r”, “r”), “ß”, “s”), “ś”, “s”), “š”, “s”), “t”, “t”), “û”, “u”), “ü”, “u”), “ù”, “u”), “ú”, “u”), “ū”, “u”), “v”, “v”), “w”, “w”), “x”, “x”), “ÿ”, “y”), “ž”, “z”), “ź”, “z”), “ż”, “z”), " “, “”), “!”, “”), “.”, “”), “/”, “”), “””, “”), “#”, “”), “$”, “”), “%”, “”), “&”, “”), “’”, “”), “(”, “”), “)”, “”), “*”, “”), “+”, “”), “,”, “”), “:”, “”), “;”, “”), “<”, “”), “=”, “”), “>”, “”), “?”, “”), “@”, “”), “[”, “”), “\”, “”), “]”, “”), “^”, “”), “_”, “”), “`”, “”), “{”, “”), “|”, “”), “}”, “”), “~”, “”), “«”, “”), “»”, “”), “€”, “”), “„”, “”), ““”, “”), “-----”, “”), “-----”, “”), “----”, “”), “—”, “”), “–”, “”)

Thanks! I’ll update https://github.com/UnlyEd/airtable-utils/blob/master/formulas/slug.js to include those :slightly_smiling_face:

Edit: Updated through https://github.com/UnlyEd/airtable-utils/pull/2

Hi, Above solution looks good, but how would you handle the duplicate text?

Curious about the solution.

Thanks in advance