Formula to replace special characters - slug creation
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!
Page 1 / 2
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…
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.
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:
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?
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:
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?
Hi Andrés, I have the same issue. What I did was add a column with the same formula you just posted. And then, I added another column which “transforms” that column into UPPERCASE. In my case I did this to get my Client Names “standard”. If you are aiming to have a url-friendly maybe you could do the same but with LOWERCASE. I hope that helps.
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.)
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.
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.)
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.
How would you write this if the substitution needed is to replace a quote mark (") with a space?
Thank you!
How would you write this if the substitution needed is to replace a quote mark (") with a space?
Thank you!
Quote marks are escaped with a backward slash:
SUBSTITUTE({Field Name}, "\\"", " ")
Quote marks are escaped with a backward slash:
SUBSTITUTE({Field Name}, "\\"", " ")
Or you can surround single quotes with double, and vice-versa:
SUBSTITUTE({Field Name},'"','')
SUBSTITUTE({Field Name},"'","")
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?
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.
Hi, Above solution looks good, but how would you handle the duplicate text?
Curious about the solution.
Thanks in advance
Hi, Above solution looks good, but how would you handle the duplicate text?
Curious about the solution.
Thanks in advance
If by “duplicated text” you mean the duplication of SUBSTITUTE within the formula, then there is no workaround that. You cannot do it differently, mostly due to the limitations of the Airtable Formulas.
Hi! I keep getting an error when using the substitute formula in AirTable. Although the formula works perfectly in excel. Maybe someone can help? I have a field of Lookup values that all contain numbered values like this:
“1. Monetization”
“2. Multiplayer”
“3. Gaming”
“4. Developer Experience”
“5. Service Platform”
“6. Verticals”
The name of the above Lookup field is “Products”
I need to remove the numbers, so I created a formula field with this formula:
I also tried a different formula that’s supposed to remove the first 3 characters but get the same error:
RIGHT({Products}, LEN({Products})-3)
Any help is much appreciated!
The issue is because the formula doesn’t work when it’s trying to do the calculation based on a Lookup field. I turned the field into a single-line-text field, and used this formula, and it works:
RIGHT(
{Operate Product Formula},
LEN(
{Operate Product Formula}
)-FIND(
'. ',
{Operate Product Formula}
)-1
)
Most of the time, lookup fields return arrays, not individual data items, so you’re getting an array of strings from it, not just a single string. The SUBSTITUTE() function only operates when fed a single string. Here’s a reference table that I built that shows more detail about lookup field output, including those rare times when it does output single values:
To work around this lookup field behavior, concatenate the lookup field’s output with an empty string, which force-converts it to a string, and then functions like SUBSTITUTE() that operate on strings will perform as expected. As an example, here’s how your above formula could be modified with this setup:
Another way to approach it would be to use one of the new regular expression functions to remove all instances of digits followed by a period-space combo.
Thanks so much @Justin_Barrett! Both options work beautifully. I’m going to go with the new regex function, that way I won’t have to keep editing the substitute formula by adding more numbers as the count goes higher than 6.
Reading through URL encoders this morning - building formulas to parse urls so that I can easily construct associated MS Flows.
Does anyone have a more elegant method of doing this? Would be great if Airtable had additional formulas for this. Another that I’ve always wanted is Base64 encode/decode.
