Formula to replace special characters - slug creation

[Jay_Adam]

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!

[TFP]

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.

[Emma_Butler]

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:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Products},“1. “,””), “2. “,””), “3. “,””), “4. “,””), “5. “,””), “6. “,””)

It gives me an #ERROR!

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!

[Emma_Butler]

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
)

[Justin_Barrett]

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:

Airtable

Airtable Field Reference - Airtable

Explore the "Airtable Field Reference" view on Airtable.

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:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Products} & "","1. ",""), "2. ",""), "3. ",""), "4. ",""), "5. ",""), "6. ","")

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.

IF(Source, REGEX_REPLACE(Source, "\\d\\.\\s", ""))

[Emma_Butler]

Emma_Butler:

{Products}

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.

[Karlstens]

Reading through URL encoders this morning - building formulas to parse urls so that I can easily construct associated MS Flows.

w3schools.com

HTML URL Encoding Reference

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

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.

REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE({URL}, 
"%2F", "/"),
"%20", " "),
"%5F", "_")

[Justin_Barrett]

It sounds like you want the exact opposite of the ENCODE_URL_COMPONENT() function. Right now your method—or a similar one using SUBSTITUTE()—is the only available option.

[Karlstens]

Yup, I thought that might be the case. Thanks for confirming - and fingers crossed we can see some more formula love this year.