Concatenate and Substitute

[Kristen_Edge]

Hi all,

I am trying to combine text from two fields using Concatenate (and that’s simple enough) but when I try to add a Substitute formula to shorten one field’s text, it doesn’t work. I am new to formulas, and am sure I’m doing something wrong here. Here are the two formulas that work on their own, but not together. Thank you for any insights!

SUBSTITUTE(RIGHT({2021-22 School Program & Membership},LEN({2021-22 School Program & Membership})-22)

CONCATENATE({Program Booked},’ / ',{School Name})

[Justin_Barrett]

Your SUBSTITUTE() function isn’t formatted correctly. The basic format is this:

SUBSTITUTE(source_string, string_to_find, substitute_string)

In that layout, substitute_string will be inserted everywhere that string_to_find is located in source_string.

Before trying to help you work up the correct formula, it would help to know more details about your end goal.

[augmented]

Hi Kirsten. Based on my answer to your prior post, I’m guessing you might want something like this:

CONCATENATE({Program Booked},’ / ',{School Name},RIGHT({2021-22 School Program & Membership},LEN({2021-22 School Program & Membership})-22))

You might want some whitespace after {School Name} as well. But that’s just a wild stab in the dark.

[Kristen_Edge]

augmented:

CONCATENATE({Program Booked},’ / ',{School Name},RIGHT({2021-22 School Program & Membership},LEN({2021-22 School Program & Membership})-22))

Thanks for your help (again!). Airtable accepted the formula but it didn’t remove the 22 characters for some reason.

[Kristen_Edge]

Hi Justin, thanks for this. Context: I am using concatenate to combine the Program Booked and School Name fields, but as you can see in the example, the Program Booked name is quite long (has to be for Salesforce integration purposes) so I’m trying to cut the first 16 characters off Program Booked and combine with School Name for the Inquiry (formula) field.

[augmented]

Interesting. It’s the same formula we used prior to remove the first 22 characters. I can see {Program Booked} and {School Name} fields from your response to Justin. Do you mind showing me a picture of the fields, {Inquiry} and {2021-22 School Program & Membership}? Stretch them out as much as possible.

[Kristen_Edge]

augmented:

CONCATENATE({Program Booked},’ / ',{School Name},RIGHT({2021-22 School Program & Membership},LEN({2021-22 School Program & Membership})-22))

Sure! So this is a different table, but similar function to the 2021-22 School Program & Membership conundrum. Here is the formula I’m using:

Here are the complete fields. Program Booked and School Name are the only fields I am pulling from.

[augmented · Accepted solution]

Now I’m confused. You started off this thread with the field {2021-22 School Program & Membership} in your formulas. Was that just an example? Do you just want to concatenate {Program Booked} (less first 16 chars) and {School Name}? If so,

CONCATENATE(RIGHT({Program Booked},LEN({Program Booked})-16),’ / ',{School Name})

Is that what you were going for?

[Kristen_Edge]

Thank you that was perfect, and sorry for the miscommunication. I’m working on numerous tables and troubleshooting (with very little background with formulas). Your help and patience are MUCH appreciated!