Replace the Last Occurrence of a String or Character in Google Sheets

Using a regex formula, you can easily replace the last occurrence of a character in Google Sheets. And yes, the same method works if you want to replace the last occurrence of a string or word.

Let’s say you want to replace the last comma, colon, semicolon, or even a pipe character in a list with another value. Here’s how to do it using REGEXREPLACE.

Table of Contents

Example:

Value in cell A1:

Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

Now, suppose you want to replace the final comma (after “Nov”) with the word “and”.

Expected Result #1:

Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov and Dec

Expected Result #2:

Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, and Dec

So how do you achieve this?

Replace the Last Occurrence of a Character in Google Sheets

To get the above results, use the REGEXREPLACE function in Google Sheets. Here’s how.

Formula #1:

=REGEXREPLACE(A1, "(.*),", "$1 and")

This replaces the last comma with the string ” and”.

Formula #2:

=REGEXREPLACE(A1, "(.*),", "$1, and ")

This one replaces the last comma with “, and ” to match expected result #2.

Replace the Last Occurrence of a Pipe Symbol

Want to replace the last occurrence of the pipe symbol (|)? Use this version:

Formula #3:

=REGEXREPLACE(A1, "(.*)\|", "$1 and")

Note: Since the pipe (|) is a special regex character, we escape it using a backslash (\|).

Regular expression to replace the last occurrence of a string or character in Google Sheets

Replace the Last Occurrence of a String in Google Sheets

Sometimes, you may want to replace the last occurrence of a full word or phrase rather than a single character. In that case, the same principle applies.

Example:

Text in A1:

John is a good student. John scored 89% marks.

Use this formula to replace the last “John” with “He”:

=REGEXREPLACE(A1, "(.*)John", "$1He")

Result:

John is a good student. He scored 89% marks

That’s how you replace the last occurrence of a string in Google Sheets.

Remove the Last Occurrence of a Word or Character

To just remove the last instance of a word (or character), modify the formula slightly:

Value in A1:

He is present present

Formula:

=REGEXREPLACE(A1, "(.*) present", "$1")

This removes the last occurrence of the word “ present”, giving you: He is present

Wrap Up

If you’re searching for a way to replace the last occurrence of a character or looking for a formula to replace the last occurrence of a string in Google Sheets, REGEXREPLACE offers a flexible, formula-based solution that doesn’t require scripting or add-ons.