What's the proper way to use the SUBSTITUTE formula for multiple options?

Kelly Gabel

@Paul Newcome I always tag you in my formula questions because you ROCK!

I need to map project phases we use in Smartsheet to the the phases we use when we report on our projects. I was able to get the basic function to work using the following formula, but I need to turn this into a string (I'm assuming an OR string) to map more than just one phase option.

Here is the basic formula that works:

=SUBSTITUTE([Active Stage]@row, "Eng. & Proc.", "Execution")

Here is the long string I attempted that becomes UNPARSEABLE:

=SUBSTITUTE([Active Stage]@row, "Site Assessment", "Initiation") SUBSTITUTE([Active Stage]@row, "Eng. & Proc.", "Execution") SUBSTITUTE([Active Stage]@row, "Pre-Construction", "Execution") SUBSTITUTE([Active Stage]@row, "Construction", "Execution") SUBSTITUTE([Active Stage]@row, "Energization", "Execution") SUBSTITUTE([Active Stage]@row, "Commissioning", "Execution") SUBSTITUTE([Active Stage]@row, "Close-out", "Close Out")

I'm hoping this is just a syntax thing and not a limitation (meaning, not possible to use in a string formula)

Thanks in advance!

Paul Newcome

I agree with @Mike Wilday. This is a case where you need to nest. Maybe this explanation will help...

When you use the SUBSTITUTE function, you are creating a text string. That text string is what you want evaluated by the SUBSTITUTE function. Here is a slightly easier version to read where we swap letters for numbers (A = 1, B = 2, C = 3, etc...).

First we swap out the A for 1.

=SUBSTITUTE([Text Column]@row, "A", "1")

Now we want to take the string that is generated by that and swap out the B with a 2.

=SUBSTITUTE(text_string, "B", "2")

=SUBSTITUTE(SUBSTITUTE([Text Column]@row, "A", "1"), "B", "2")

Now we want to take THAT text string and swap the C with a 3.

=SUBSTITUTE(text_string, "C", "3")

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Text Column]@row, "A", "1"), "B", "2"), "C", "3")

See the pattern? We only reference "[Text Column]@row" once. Then we have some SUBSTITUTE functions on the left and the same number of "swaps" on the right.

So let's get building... We start with our cell reference:

=[Active Stage]@row

Now we do our first swap:

=SUBSTITUTE([Active Stage]@row, "Site Assessment", "Initiation")

Now we add a SUBSTITUTE to the left and a swap to the right:

=SUBSTITUTE(text_string, "Eng. & Proc.", "Execution")

=SUBSTITUTE(SUBSTITUTE([Active Stage]@row, "Site Assessment", "Initiation"), "Eng. & Proc.", "Execution")

And another SUBSTITUTE swap...

=SUBSTITUTE(text_string, "Pre-Construction", "Execution")

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Active Stage]@row, "Site Assessment", "Initiation"), "Eng. & Proc.", "Execution"), "Pre-Construction", "Execution")

And we just keep going until we either cover all of the swaps or we hit 4,000 characters.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Active Stage]@row, "Site Assessment", "Initiation"), "Eng. & Proc.", "Execution"), "Pre-Construction", "Execution"), "Construction", "Execution"), "Energization", "Execution"), "Commissioning", "Execution"), "Close-out", "Close Out")

Now for a quick note...

This one isn't too bad, but what if you have a long list of things to swap out? For example, you want to swap all letters with numbers PLUS you want to pull out all special characters. That's 26 SUBSTITUTE functions nested together plus the additional functions for pulling the special characters.

Or...

You can build out a table and use an INDEX/MATCH. Now you have a very short formula that is going to end up being much more efficient, PLUS it is much easier to manage if you need to add, remove, or change anything because now all you have to do is update a table instead of having to fight with the formula itself.

Kelly Gabel

Wow...this totally makes sense now! Thank you! For now, I will use the SUBSTITUTE formula, but I will definitely be researching the INDEX/MATCH table because that will come in very handy in the future.

Thanks again Paul!! So helpful, as always!

Paul Newcome

@Kelly Gabel For the INDEX/MATCH, you would build out a table like this:

And the formula would look something like this:

=INDEX({Replacement Values Column}, MATCH([Active Stage]@row, {Original Values Column}, 0))

Then you can manage all of your substitutions by simply editing the table instead of the formula. They wouldn't even have to be in chronological order since we are matching on the "Original Value".

@Mike Wilday No worries. We've all done that. Hahaha.

And thanks. I wanted to be able to explain it even to the newest of Smartsheet users because it really is SO much better than a VLOOKUP, so I actually put a bit of thought and effort into how I break it down so it isn't too complicated but still gets the detailed points across.

Mike Wilday

Hmmm. It looks like those would need to be nested formulas, not just a long string of the formulas running together.

Try something like this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Active Stage]@row, "Site Assessment", "Initiation"), [Active Stage]@row, "Eng. & Proc.", "Execution"),[Active Stage]@row, "Pre-Construction", "Execution"), [Active Stage]@row, "Construction", "Execution"),[Active Stage]@row, "Energization", "Execution") , [Active Stage]@row, "Commissioning", "Execution") ,[Active Stage]@row, "Close-out", "Close Out")

Did that work?

Kelly Gabel

Mike,

Great point but that resulted in an INVALID DATA TYPE unfortunately...

Paul Newcome

I agree with @Mike Wilday. This is a case where you need to nest. Maybe this explanation will help...

When you use the SUBSTITUTE function, you are creating a text string. That text string is what you want evaluated by the SUBSTITUTE function. Here is a slightly easier version to read where we swap letters for numbers (A = 1, B = 2, C = 3, etc...).

First we swap out the A for 1.

=SUBSTITUTE([Text Column]@row, "A", "1")

Now we want to take the string that is generated by that and swap out the B with a 2.

=SUBSTITUTE(text_string, "B", "2")

=SUBSTITUTE(SUBSTITUTE([Text Column]@row, "A", "1"), "B", "2")

Now we want to take THAT text string and swap the C with a 3.

=SUBSTITUTE(text_string, "C", "3")

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Text Column]@row, "A", "1"), "B", "2"), "C", "3")

See the pattern? We only reference "[Text Column]@row" once. Then we have some SUBSTITUTE functions on the left and the same number of "swaps" on the right.

So let's get building... We start with our cell reference:

=[Active Stage]@row

Now we do our first swap:

=SUBSTITUTE([Active Stage]@row, "Site Assessment", "Initiation")

Now we add a SUBSTITUTE to the left and a swap to the right:

=SUBSTITUTE(text_string, "Eng. & Proc.", "Execution")

=SUBSTITUTE(SUBSTITUTE([Active Stage]@row, "Site Assessment", "Initiation"), "Eng. & Proc.", "Execution")

And another SUBSTITUTE swap...

=SUBSTITUTE(text_string, "Pre-Construction", "Execution")

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Active Stage]@row, "Site Assessment", "Initiation"), "Eng. & Proc.", "Execution"), "Pre-Construction", "Execution")

And we just keep going until we either cover all of the swaps or we hit 4,000 characters.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Active Stage]@row, "Site Assessment", "Initiation"), "Eng. & Proc.", "Execution"), "Pre-Construction", "Execution"), "Construction", "Execution"), "Energization", "Execution"), "Commissioning", "Execution"), "Close-out", "Close Out")

Now for a quick note...

This one isn't too bad, but what if you have a long list of things to swap out? For example, you want to swap all letters with numbers PLUS you want to pull out all special characters. That's 26 SUBSTITUTE functions nested together plus the additional functions for pulling the special characters.

Or...

You can build out a table and use an INDEX/MATCH. Now you have a very short formula that is going to end up being much more efficient, PLUS it is much easier to manage if you need to add, remove, or change anything because now all you have to do is update a table instead of having to fight with the formula itself.

Kelly Gabel

Wow...this totally makes sense now! Thank you! For now, I will use the SUBSTITUTE formula, but I will definitely be researching the INDEX/MATCH table because that will come in very handy in the future.

Thanks again Paul!! So helpful, as always!

Paul Newcome

Happy to help. 👍️

INDEX/MATCH can seem a little daunting at first, but here is a breakdown of how it works (it replaces a VLOOKUP with much more flexibility and efficiency).

=INDEX(range_to_pull_from, row_number, optional_column_number)

When dealing with a two column reference table, the first and last portions of the INDEX function are easy. The first is going to be the column you want to pull from, and the last can be left out entirely since you are only pulling from a single column.

The only real "tricky" part is automating which row number to pull from.

That's when the MATCH come into play. MATCH returns a number based on where within a table the match is found. If you are matching within a single column, then it is going to return the row number where the match is found.

=MATCH("text to match", range_to_match_in, 0)

That third portion I always recommend entering zero in cases like this because it indicates an exact match. The first portion is going to be what you want to match within the list. In this particular case it is [Active Stage]@row, and then the second portion is going to be the column that contains the data you are matching to (to get the row number).

=INDEX({Column Housing Desired Output Values}, MATCH([Active Stage]@row, {Column Housing Stage Variables}, 0))

Kelly Gabel

Ok. So I think I'm understanding that concept fairly well...but where does the substituted text fit into this method?

Mike Wilday

Your table would be your substitutions. You have in column 1 each of your original settings, then in column 2 would be your substitutions for each of those words, Your index/match function would take the keyword from your sheet, find the match in your table and input the keyword you have in column 2. It's very similar to a vlookup process.

Mike Wilday

@Paul Newcome Thanks for correcting that. I had gotten so fixated on using her data that I forgot my test didn't include the word multiple times. Good explanations too of the index/match statement!

Paul Newcome

@Kelly Gabel For the INDEX/MATCH, you would build out a table like this:

And the formula would look something like this:

=INDEX({Replacement Values Column}, MATCH([Active Stage]@row, {Original Values Column}, 0))

Then you can manage all of your substitutions by simply editing the table instead of the formula. They wouldn't even have to be in chronological order since we are matching on the "Original Value".

@Mike Wilday No worries. We've all done that. Hahaha.

And thanks. I wanted to be able to explain it even to the newest of Smartsheet users because it really is SO much better than a VLOOKUP, so I actually put a bit of thought and effort into how I break it down so it isn't too complicated but still gets the detailed points across.

Kelly Gabel

@Paul Newcome This Index/Match is such a game changer! I decided to go this route as it will lead to MUCH less maintenance over time! Thank you for taking the time to explain it so clearly!!

Paul Newcome

@Kelly Gabel Happy to help. 👍️