Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 12, 2022 01:15 PM
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:
Solved! Go to Solution.
Oct 13, 2022 05:47 AM
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).*","")
Oct 12, 2022 02:48 PM
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:
[\\,\\-\\&\\;\\(].*
[\\,\\-\\&\\;\\(]|(AND).*
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).*","")
Oct 13, 2022 01:08 AM
@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
Oct 13, 2022 05:47 AM
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).*","")
Oct 13, 2022 06:57 AM
Woow, thank you very much, it works :pray:
Oct 13, 2022 09:07 AM
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?