Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Formula that will remove a text after multiple characters

Topic Labels: Formulas
Solved
Jump to Solution
4477 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Mart_Kolyshkina
5 - Automation Enthusiast
5 - Automation Enthusiast

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:
image

1 Solution

Accepted Solutions
Nathaniel_Grano
8 - Airtable Astronomer
8 - Airtable Astronomer

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).*","")

See Solution in Thread

5 Replies 5
Nathaniel_Grano
8 - Airtable Astronomer
8 - Airtable Astronomer

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).*","")

@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
8 - Airtable Astronomer
8 - Airtable Astronomer

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).*","")

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

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?