Formula to Trim and Replace Text via Formula

[Kristen_Edge]

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.

[augmented]

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.

[Kristen_Edge]

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

[augmented]

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

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.

[Kristen_Edge]

augmented:

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

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