Get three formulas to extract, replace, and match the nth occurrence of a string or number in a phrase in Google Sheets. The formulas are based on REGEXEXTRACT, REGEXREPLACE, SUBSTITUTE, and REGEXMATCH.

For replacing and matching the nth occurrence, we’ll use a specific word or number for the match or replacement. But when it comes to extracting, there’s no point in extracting the nth occurrence of a specific word, right?

So here’s what we’re going to do using three (or more) different formulas in Google Sheets:

• Extract the nth occurrence of a non-specific word or number
• Replace the nth occurrence of a specific word or number
• Match the nth occurrence of a specific word or number

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

REGEXMATCH to Match the Nth Occurrence in Google Sheets

Here’s the regex pattern to match the nth occurrence of a string or number using the REGEXMATCH function:

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

Replace text with the string or number you want to match, and n with the desired occurrence number.

Match Second Occurrence of a String

To match the second occurrence of the word "apple" in cell A1:

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

This formula is case-insensitive due to (?i). To make it case-sensitive, just remove that part:

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

If "apple" appears twice or more in A1, the formula returns TRUE. If not, it returns FALSE.

Here’s a clever way to check for the nth occurrence without using regex. It works by comparing the original string to a modified version with the nth occurrence removed:

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

This returns TRUE if "apple" appears at least twice in A1.

Match Second Occurrence of a Number

You can also match the nth occurrence of a number using the same structure. For example, to match the second occurrence of 4.5:

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

This checks if 4.5 appears at least twice in the string.

Replace the Nth Occurrence in Google Sheets

To replace the nth occurrence of a string or number in Google Sheets, you can use either the SUBSTITUTE or REGEXREPLACE function depending on the need.

Option 1: SUBSTITUTE Function

The SUBSTITUTE function has a built-in parameter for occurrence:

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

Replace Second Occurrence of a Word

Let’s say cell A1 contains:

France, Germany, France, Italy

To remove the second occurrence of "France":

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

Output:

France, Germany, , Italy

Note: SUBSTITUTE is case-sensitive.

Replace Second Occurrence of a Number

If A1 contains:

10, 20, 20, 30

Use:

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

Output:

10, 20, 30

Option 2: REGEXREPLACE Function

If you want a case-insensitive or pattern-based replacement, REGEXREPLACE is more powerful — though a bit trickier.

Replace the 2nd Occurrence of “apple” with “orange”

=REGEXREPLACE(A1, "(?i)((?:.*?apple){1})(.*?)apple", "$1$2orange")

Explanation:

• (?:.*?apple){1} matches the first occurrence.
• (.*?)apple matches the second.
• $1$2orange keeps everything before and between the matches, replacing only the second "apple".

To replace the nth occurrence, change {1} to {n-1}. For example:

• To replace the 4th occurrence, use {3}.

Keep in mind: This approach assumes somewhat regular structure in your data.

Extract the Nth Occurrence in Google Sheets

Let’s now extract a word or number based on its position (nth occurrence) within a string.

Extract the Nth Number Using REGEXEXTRACT

Say A1 contains:

I have 20 pens and 30 pencils

To extract the second number, use:

=REGEXEXTRACT(A1, "(?:.*?(-?\d+(?:\.\d+)?)){2}")

To extract the nth number, replace {2} with {n}. Example: {4} for the 4th number.

Extract the Nth Word or Value (Text or Number)

To extract the second occurrence of any alphanumeric word (text or number):

=REGEXEXTRACT(A1, "(?:.*?([A-Za-z0-9.-]+)){2}")

This will extract the second word, regardless of whether it’s a number or plain text.

Summary

Use these formulas in Google Sheets to extract, replace, or match the nth occurrence of a string or number inside a cell — not across rows or ranges, but within a single string. Choose between REGEXMATCH, REGEXEXTRACT, REGEXREPLACE, and SUBSTITUTE depending on what you’re trying to achieve.

Related Reading

• Replace the Last Occurrence of a String or Character in Google Sheets
• Extract Last N Values from a Delimiter-Separated String in Google Sheets
• Remove or Replace Last Character from a String in Google Sheets
• Extract First, Last and Middle Names in Google Sheets