Formula to replace special characters - slug creation

Jay_Adam · ‎Aug 17, 2018

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!

Justin_Barrett · ‎Mar 29, 2019

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

kpathare · ‎Mar 29, 2019

That worked - thanks so much Justin! :slightly_smiling_face:

Ambroise_Dhenai · ‎May 13, 2020

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

Ambroise_Dhenai · ‎May 13, 2020

@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 · ‎May 13, 2020

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:

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:

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

Justin_Barrett · ‎May 14, 2020

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

Karlstens · ‎Jun 14, 2020

That works well, thank you!

Carlos_Hurtado · ‎Jun 23, 2020

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”), " “, “”), “!”, “”), “.”, “”), “/”, “”), “””, “”), “#”, “”), “$”, “”), “%”, “”), “&”, “”), “’”, “”), “(”, “”), “)”, “”), “*”, “”), “+”, “”), “,”, “”), “:”, “”), “;”, “”), “<”, “”), “=”, “”), “>”, “”), “?”, “”), “@”, “”), “[”, “”), “\”, “”), “]”, “”), “^”, “”), “_”, “”), “`”, “”), “{”, “”), “|”, “”), “}”, “”), “~”, “”), “«”, “”), “»”, “”), “€”, “”), “„”, “”), ““”, “”), “-----”, “”), “-----”, “”), “----”, “”), “—”, “”), “–”, “”)

Ambroise_Dhenai · ‎Jul 29, 2020

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

shravan_chandra · ‎Dec 14, 2020

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

Curious about the solution.

Thanks in advance