Skip to main content

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 🙂

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:


g\\,\\-\\&\\;\\(]|(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},"E\\,\\-\\&\\;\\(]|(AND).*","")

EDIT: corrected formula


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

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:


g\\,\\-\\&\\;\\(]|(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},"E\\,\\-\\&\\;\\(]|(AND).*","")

EDIT: corrected formula


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

@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

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},"(l\\,\\-\\&\\;\\(\\/]|AND|and).*","")

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},"(l\\,\\-\\&\\;\\(\\/]|AND|and).*","")


Woow, thank you very much, it works 🙏

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},"(l\\,\\-\\&\\;\\(\\/]|AND|and).*","")

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?

Reply


Terms & Conditions