Formula that will remove a text after multiple characters

[Mart_Kolyshkina]

Hey guys,

Can you please suggest to me a formula that will remove a text after multiple characters ( ‘’,‘’ ‘’/‘’ ‘’ - ‘’ ‘‘and’’ ‘’&‘’ ‘’;‘’ ‘’(‘’.
I already found on this Forum the formula that removes a text after one single character,

LEFT(
{Column name},
FIND(
“-”,
{Column name}
)-1
)

Can you please advise me on how to add to this formula options for other characters, or maybe the new formula that will have a such workflow, thanks in advance :slightly_smiling_face:

[Nathaniel_Grano]

You should be able to achieve what you are looking for by using Regular Expressions.
Airtable has three functions available that use Regular Expressions:
REGEX_MATCH
REGEX_EXTRACT
REGEX_REPLACE
You can read about them here: Guide to REGEX() Functions | Airtable Support

We can construct a regular expression that will match substrings that begin with any of the characters you listed. In airtable’s syntax that expression looks like this:

[\\,\\-\\&\\;\\(].*

• The square brackets indicate that airtable should match any one of the characters inside the brackets.
• Since we’re dealing with punctuation characters we have to use \\ to escape those characters so the search will function properly
• Since the “AND” that you want to catch is more than one character, we should account for it a different way:

[\\,\\-\\&\\;\\(]|(AND).*

• The | character means logical or in this case
• The parenthesis help keep the word “AND” grouped together
• The period symbol means “match any character” and the asterisk means “repeat zero or more times”

Now we can put this expression into the REGEX_REPLACE function to do our work. This formula should do the trick:

REGEX_REPLACE({Job title},"[\\,\\-\\&\\;\\(]|(AND).*","")

EDIT: corrected formula

REGEX_REPLACE({Job title},"([\\,\\-\\&\\;\\(]|AND).*","")

[Mart_Kolyshkina]

@Nathaniel_Granor thank you very much for your advice, but I don’t need just to extract those characters I also need to extract the text after those characters:
COO and Director - COO
COO/Director - COO
COO - Director - COO
COO, Director - COO

[Nathaniel_Grano · Accepted solution]

Whoops! I had parenthesis in the wrong place in the regular expression:

REGEX_REPLACE({Job title},"([\\,\\-\\&\\;\\(]|AND).*","")

I also noticed that I left out the slash character. Also from your example, it looks like you want to catch lower-case ‘and’, so…

REGEX_REPLACE({Job title},"([\\,\\-\\&\\;\\(\\/]|AND|and).*","")

[Mart_Kolyshkina]

Nathaniel_Granor:

REGEX_REPLACE({Job title},"([\\,\\-\\&\\;\\(\\/]|AND|and).*","")

Woow, thank you very much, it works :pray:

[Mart_Kolyshkina]

I have one more question, do you know how can I add to this formula one more formula that will make all words begin with capital letters, and other letters will be small?

I used before for other columns this formula for lowercase and uppercase letters:
REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(LOWER({First Name (DM)}), “^a”, “A”), “^b”, “B”), “^c”, “C”), “^d”, “D”), “^e”, “E”), “^f”, “F”), “^g”, “G”), “^h”, “H”), “^i”, “I”), “^j”, “J”), “^k”, “K”), “^l”, “L”), “^m”, “M”), “^n”, “N”), “^o”, “O”), “^p”, “P”), “^q”, “Q”), “^r”, “R”), “^s”, “S”), “^t”, “T”), “^u”, “U”), “^v”, “V”), “^w”, “W”), “^x”, “X”), “^y”, “Y”), “^z”, “Z”), " a", " A"), " b", " B"), " c", " C"), " d", " D"), " e", " E"), " f", " F"), " g", " G"), " h", " H"), " i", " I"), " j", " J"), " k", " K"), " l", " L"), " m", " M"), " n", " N"), " o", " O"), " p", " P"), " q", " Q"), " r", " R"), " s", " S"), " t", " T"), " u", " U"), " v", " V"), " w", " W"), " x", " X"), " y", " Y"), " z", " Z")

But how can I combine them, and make not just one word begin with a capital letter, but all words in a column combined with a formula that you created for me before?