This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Other questions
- Re: Formula to replace special characters - slug c...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

0
9539
0

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!

Reply

27 Replies 27

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 21, 2018 02:08 PM

Well, you can fit it all into one formula with nested substitutions… not too hairy but still have to add each slug. Got you started…

`SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({English Title},"!",""),"#",""),"*","")`

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 21, 2018 05:29 PM

If it doesn’t have to be instant you can do this with Zapier pretty easily. Their text formatting action is quite powerful and they offer a free plan, so if you don’t mind waiting 5-15 minutes for Zapier to process the titles for you (or you don’t mind pressing a button each time you need it to happen) then you won’t have to next a bunch of substitutions. That definitely works, but I know how messy it can get when you have a lot of things nested and, personally, I start making typos when there’s too much going on in the formula entry area. It’s so small and you can’t indent! Hopefully that’ll change in the future, though.

Anyway, if you’re not sure how to do this in Zapier and want to try, just let me know and I’ll walk you through it. :slightly_smiling_face:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 29, 2018 02:04 AM

I wonder if there is another way to achieve this without using Zapier, since I need instant re-formatting of addresses in Spanish to a url-friendly format, and I’ve found myself doing this:

```
`SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(venue_full_address,"á","a"),"é","e"),"í","i"),"ó","o"),"ú","u"),"ü","u"),".",""),"ñ","n")`
```

And still need to keep going with all the capital letters, symbols, and so on… It doesn’t look quite right to have such a big formula, even though it actually works.

Any ideas?

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 31, 2018 06:22 PM

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 01, 2018 08:22 AM

This formula will lowercase your URLs and replace special characters. (Currently, it replaces space characters with `%20`

, so you’ll want to change that to a hyphen.)

```
LOWER(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE({name},"%","%25"),
" ","%20"),
"!","%21"),
"#","%23"),
"$","%24"),
"'","%27"),
"(","%28"),
")","%29"),
"*","%2A"),
"+","%2B"),
",","%2C"),
":","%3A"),
";","%3B"),
"=","%3D"),
"@","%40"),
"[","%5B"),
"]","%5D"))
```

@Andres_Gomez

As @Kenneth_Sly mentions, surrounding your original text field with either `UPPER()`

or `LOWER()`

cuts the amount of work you need do by half. Beyond that, you’re on the right track by defining a bunch of nested `SUBSTITUTES()`

. For a recent project, I had to build a query system that could search an international database of names, which required me to wrap the field to be matched with innumerable `SUBSTITUTES()`

s replacing accented, Germanic, and Scandinavian characters with their English approximations. (While the data to be searched was often sourced internationally, I had the luxury of assuming the search terms, at least, would be entered by employees at the client’s New York-based headquarters, using plain old U.S. ASCII keyboards.)

Fortunately, Airtable appears to handle `SUBSTITUTE()`

s with relatively little overhead; in tests I’ve performed comparing using a multiply-substituted text value in a calculation versus using a ‘pre-cooked’ version after all such substitutions had been performed, the difference in processing speed was indiscernible to the user; network delay, PC and browser load, and overall Airtable response time all caused greater variation in perceived lag. Even the `SUBSTITUTE()`

-laden formula for my Soundex implementation seems to operate friskily enough.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 13, 2019 06:35 PM

How would you write this if the substitution needed is to replace a quote mark (") with a space?

Thank you!

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 13, 2019 08:04 PM

Quote marks are escaped with a backward slash:

`SUBSTITUTE({Field Name}, "\"", " ")`

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 14, 2019 02:15 AM

`SUBSTITUTE({Field Name},'"','')`

`SUBSTITUTE({Field Name},"'","")`

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 29, 2019 02:14 PM

I want to prefill a form with contents of a long text field.

I have this formula - what do I need to change to accommodate a long text field?

SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( {Prefill Value} ,"%","%25") ,"!","%21") ," “,”%20") ,"#","%23") ,"$","%24") ,"&","$26") ,"’","%27") ,"(","%28") ,")","%29") ,"*","%2A") ,"+","%2B") ,",","%2C") ,"/","%2F") ,":","%3A") ,";","%3B") ,"=","%3D") ,"?","%3F") ,"@","%40") ,"[","%5B") ,"]","%5D") ,’"’,"%22") ,"-","%2D") ,".","%2E") ,"<","%3C") ,">","%3E") ,"^","%5E") ,"_","%5F") ,"`","&60") ,"{","%7B") ,"|","%7C") ,"}","%7D") ,"~","%7E") ,’\’,"%5C")

Thanks,

Kalindi

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 29, 2019 04:40 PM

For a long text field, you need to add another `SUBSTITUTE`

layer that replaces `\n`

with `%0A`

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 29, 2019 05:23 PM

That worked - thanks so much Justin! :slightly_smiling_face:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 13, 2020 06:30 AM

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 14, 2020 07:07 AM

`ENCODE_URL_COMPONENT()`

function was added to Airtable.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 14, 2020 04:36 AM

That works well, thank you!

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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