Get three formulas to extract, replace, and match the nth occurrence of a string/number in a phrase in Google Sheets. The formulas are based on Regexextract, Substitute, and Regexmatch respectively.

For replacing and matching nth occurrence, of course, we will use a specific word or number for the nth match/replace. But there is no point in extracting nth occurrence of a specific word, right?

So what we are going to do with our three (or more) different Google Sheets formulas are;

• Extracting Nth Occurrence of a Non-Specific Word/Number.
• Replacing Nth Occurrence of a Specific Word/Number.
• Matching Nth Occurrence of a Specific Word/Number

Let’s begin with matching the nth occurrence of a string/number in Google Sheets.

RegexMatch Function to Match Nth Occurrence in Google Sheets

Here is the regular_expression, I mean the regex pattern, to match the nth occurrence of a number using RegexMatch in Google Sheets.

(?i)(?:.*?text){n}

Replace text with the ‘text’ that you want to match and ‘n’ with the number of occurrences.

Match Second Occurrence of a String

The following Regexmatch formula would match the second occurrence of the string ‘apple’ in cell A1.

=REGEXMATCH(A1,"(?i)(?:.*?apple){2}")

The above formula is case insensitive. To make this regular_expressin case sensitive remove (?i) from the pattern.

The formula returns TRUE since the string ‘apple’ has the second occurrence in cell A1. If you put 4, then the formula would return FALSE as there is no fourth occurrence of the said string.

For an alternative solution to this formula please scroll down to see the “Bonus Tip”.

Match Second Occurrence of a Number

Here there are no changes in the formula except the number (text pattern) to match.

=REGEXMATCH(A1,"(?i)(?:.*?4.5){2}")

Formula matches the second occurrence of the number 4.5.

Replace Nth Occurrence with the Substitute Function in Google Sheets

Instead of Regex, I am using the Substitute function to replace the nth occurrence of a string or number in Google Sheets.

If you check the syntax of the Substitute function, you can understand that one of the argument in this function is the occurrence_number.

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

That means this Google Sheets function is built in to substitute the nth occurrence.

Replace Second Occurrence

Example:

Formula replaces the second occurrence of the country name ‘France’ with blank. This formula is case sensitive.

=SUBSTITUTE(A1,"France","",2)

Using this Substitute formula you can replace the nth occurrence of a number too.

Value in cell A1:

10, 20, 20, 30

Formula:

=SUBSTITUTE(A1,"20,","",2)

Output:

10, 20, 30

Bonus Tip – Matching the Nth Occurrence in the Substitute Way

We can use the Substitute function in Google Sheets for matching nth occurrence! Do you know how?

You can replace the Regexreplace formula under the above subtitle “Match Second Occurrence of a String” with my following Substitute formula.

=A1<>SUBSTITUTE(A1,"apple","",2)

RegexExtract Nth Occurrence of a Number in Google Sheets

I have the following phrase in a cell in Google Sheets.

“I have 20 pens and 30 pencils”.

If the above phrase is in cell A1, to extract the second number which is 30 from this, we can use the Regexextract function as follows in Google Sheets.

Extract Second Occurrence

=regexextract(A1,"(?:[^\d]*(\d+)){2}")

Since Regex is a text function, the returned number, i.e. 30, would be text formatted. Want the Regexextract output in number? Then use this formula.

=regexextract(A1,"(?:[^\d]*(\d+)){2}")*1

To change the second occurrence to nth, replace {2} with the number you want. I mean type {5} for the 5th occurrence.

If you simply want the second occurrence of a value (irrespective of the value, it can be a number or text) then use this Regex.

=regexextract(A1,"(?:\W*(\w+)){2}")

Use the above formulas to meet your requirements of extracting, replacing, and matching the nth occurrence of a value in Google Sheets.

Related Reading: Regex to Replace the Last Occurrence of a Character in Google Sheets.