Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 17, 2018 09:45 AM
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!
Mar 29, 2019 04:40 PM
For a long text field, you need to add another SUBSTITUTE
layer that replaces \n
with %0A
Mar 29, 2019 05:23 PM
That worked - thanks so much Justin! :slightly_smiling_face:
May 13, 2020 06:30 AM
There should be an easy-to-use function for this, really. It’s so common to generate slugs for url-related things.
May 13, 2020 07:20 AM
@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
May 13, 2020 07:35 AM
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.
May 14, 2020 07:07 AM
The formula posted by @W_Vann_Hall was made in September of 2018, before the ENCODE_URL_COMPONENT()
function was added to Airtable.
Jun 14, 2020 04:36 AM
That works well, thank you!
Jun 23, 2020 02:02 PM
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”), " “, “”), “!”, “”), “.”, “”), “/”, “”), “””, “”), “#”, “”), “$”, “”), “%”, “”), “&”, “”), “’”, “”), “(”, “”), “)”, “”), “*”, “”), “+”, “”), “,”, “”), “:”, “”), “;”, “”), “<”, “”), “=”, “”), “>”, “”), “?”, “”), “@”, “”), “[”, “”), “\”, “”), “]”, “”), “^”, “”), “_”, “”), “`”, “”), “{”, “”), “|”, “”), “}”, “”), “~”, “”), “«”, “”), “»”, “”), “€”, “”), “„”, “”), ““”, “”), “-----”, “”), “-----”, “”), “----”, “”), “—”, “”), “–”, “”)
Jul 29, 2020 07:53 AM
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
Dec 14, 2020 11:53 PM
Hi, Above solution looks good, but how would you handle the duplicate text?
Curious about the solution.
Thanks in advance