Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Formula to Trim and Replace Text via Formula

Topic Labels: Formulas
414 4
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi all, I am terrible with formulas, and have spent way too long looking through previous posts and reading up on Replace and Substitute and Find formulas. I have a column with various names for programs. I would like to cut the first 22 characters of the title and replace any instance of New School with “New/Comprehensive” for Salesforce upload purposes. Any idea where I’m going wrong here?

REPLACE({2021-22 School Program & Membership},1,22,""), FIND(“New School”,{2021-22 School Program & Membership}),17,“New/Comprehensive”)

I’ve also tried variations of combining Replace with Substitute. Both work on their own but I can’t find a way to combine them in one field. This may be impossible, but the more likely scenario is user error on my part.

4 Replies 4

Hi Kristen. Give this a try…

SUBSTITUTE(RIGHT({2021-22 School Program & Membership},LEN({2021-22 School Program & Membership})-22),‘New School’, ‘New/Comprehensive’)

Remember to make sure all of your quotes are straight ' not ’. Good luck.

Thanks for your suggestion, but unfortunately Airtable doesn’t like it. Any other ideas?
image

Hmm…the single quotes look different in my formula for some reason. My “txt” field is your {2021…} field.

image

Can you make sure you’re using the key to the left of the key? At least that’s the one I use on my laptop. You can also use double quotes, but type them in yourself.

It worked!! Thanks so much - good to know that I need to keep a closer eye on those apostrophes.