Skip to main content

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.

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.

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?

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


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.

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.



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

Reply


Terms & Conditions