Can we use the Substitute function to replace the nth match of a delimiter from the end of a string in Google Sheets?

If you are searching for an answer to this query, I have the answer (formula) for you. The formula will also work in Excel as no regular expressions are used.

But to answer this you must first clearly understand all the arguments (there are three arguments) in the syntax of the said function.

As you can see below, the syntax of the SUBSTITUTE function has an optional argument called ‘occurrence_number’. It’s optional that’s why it is inside the square brackets.

SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

Normally the majority of us ignore this optional Substitute function argument, right?

It, i.e. the occurrence_number, actually helps us to substitute the nth occurrence from the beginning of a string, not from the end. Please see the following example.

Cell A2 contains the vale “Jan / Feb / Mar / Apr / may / Jun / Jul / Aug / Sept / Oct / Nov / Dec” which is obviously the name of the 12 months separated by forward slashes.

The following Substitute formula would replace the 5th occurrence of the forward-slash from the beginning of the string with an asterisk sign.

=substitute(A2,"/","*",5)

The output would be “Jan / Feb / Mar / Apr / may * Jun / Jul / Aug / Sept / Oct / Nov / Dec”.

As you can see in the result, the fifth forward slash delimiter has been replaced by the asterisk character (delimiter).

What about substituting the 5th match or 5th occurrence of the forward-slash with an asterisk from the end of the above string?

Substitute Nth Match/Occurrence from the End of a String – Logic and Formula

In this post, how to substitute nth match from the end of the string in Google Sheets, you can learn that trick or we can say workaround. It’s quite easy to follow.

The logic here is quite simple to understand. I’ll explain the logic first as it’s important to know and then go to the formula.

Logic Part

To substitute nth occurrence from the end of a string in Google Sheets, first, you need to count the total occurrence of that specific character aka delimiter.

Then subtract the number of occurrences from the end/last of the string from this count. To this count, add the number one. For more clarity, here is the generic version of the formula.

Generic Formula:

nth_match_of_delimiter_from_the_end_of_the_string = Count_of_delimiter - n + 1

That’s the logic part. Now time to code the formula as per the logic above in a few steps.

Formula Part

Assume the total occurrence of the forward-slash is 11 (there are 11 forward-slashes in the string in cell A2). Of course, you will get the formula to get this number after a few paragraphs below.

Then, for example, if you want to substitute the nth occurrence from the end of this string in Google Sheets where the ‘n’ is 3, use 11-2 (11-3+1), i.e. 9, as the occurrence_number.

That means the occurrence_number argument in the Substitute formula would be 9.

How to count the occurrence of the forward slashes in the string in cell A2?

There are two formulas for that. You can choose either of the ones.

Formula # 1:

=len(regexreplace(A2,"[^\/]",""))

Formula # 2:

=LEN(A2)- LEN(SUBSTITUTE(A2,"/",""))

Related: Count a Specific Character in Google Sheets.

As per my value in cell A2, both the formulas would return 11 which is the total occurrence of the forward slash in the string.

I’ll proceed with the formula # 2. That means I’ll use =LEN(A2)- LEN(SUBSTITUTE(A2,"/",""))-n+1 as the occurrence_number in Substitute.

Here is the formula to Substitute the nth (here 3rd) match from the end of the string in cell A2 in Google Sheets.

=substitute(A2,"/","*",LEN(A2)- LEN(SUBSTITUTE(A2,"/",""))-2)

Counting Delimiter from the End of a String and Split

You have learned how to substitute the nth match of a character/delimiter from the end/back of a string. Where will this come in use in real-life?

You have a list in a column that contains words separated by any specific characters. When you want to extract the last ‘n’ words, we can use the above method.

First, we will replace the nth occurrence of the delimiter from the back of the string with any other delimiter as detailed above. Then split from the nth position from the back.

For example, wrapping the above formula with SPLIT as below would split the string in cell A2 into two parts.

=split(substitute(A2,"/","*",LEN(A2)- LEN(SUBSTITUTE(A2,"/",""))-2),"*")

If you want only either of the portion, use the INDEX function.

First Part:

=index(split(substitute(A2,"/","*",LEN(A2)- LEN(SUBSTITUTE(A2,"/",""))-2),"*"),0,1)

Second part:

=index(split(substitute(A2,"/","*",LEN(A2)- LEN(SUBSTITUTE(A2,"/",""))-2),"*"),0,2)

Conclusion

If your purpose of substituting nth match of a delimiter from the end of a string in Google Sheets is extracting part of a string as above, I mean count a delimiter from the end of the string, replace it, then split and extract the required portion, then there is another simple formula.

I will share that formula with you in my next tutorial.

Before concluding one more thing. We can use the Substitute formula in a range/array also. For that use the ArrayFormula function.

=ArrayFormula(substitute(A2:A4,"/","*",LEN(A2:A4)- LEN(SUBSTITUTE(A2:A4,"/",""))-2))

That’s all. Enjoy!

Resources:

• Google Sheets Regexreplace Function How to and Examples.
• Replace Function in Google Sheets – Formula Examples.
• Replace Multiple Comma Separated Values in Google Sheets.
• Regex to Replace the Last Occurrence of a Character in Google Sheets.
• Extract, Replace, Match Nth Occurrence of a String or Number in Google Sheets.
• RegexReplace to Wrap Numbers in Brackets and Its Use in Query.