Using a Regex function you can replace the last occurrence of a character in Google Sheets. Needless to say, you can use the same formula to extract the last occurrence of a word.

Sometimes you may want to replace the last occurrence of a character, for example, a colon, semicolon, comma or pipe with another character.

Example:

Value in cell A1: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

I want to replace the comma after “Nov” with the string “and” and the result should be as follows.

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

How to achieve this?

Here we can use Google Sheets REGEXREPLACE function. How? Let me explain that below.

Regular Expression to Replace the Last Occurrence of a Character in Google Sheets

As mentioned above, we can use a REGEXREPLACE formula here.

To get the above-expected result # 1, use this RegexReplace formula.

Formula # 1:

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

If you want to get the expected result # 2, then the RegexReplace formula would be as below.

Formula # 2:

=regexreplace(A2, "(.*),", "$1, and ")

Here in the first formula, I have replaced the last comma with the string “and”. But in the last formula, I have used the string “, and” to replace the comma.

Replace Pipe Symbol with a String

If you want to replace the last occurrence of a | (pipe) symbol, then the regular expression will be as follows.

Formula # 3:

=regexreplace(A3, "(.*)\|", "$1 and")

Since the Pipe symbol is a regular expression operator, we must use a backslash to escape it. See the regular expression in the above formula to understand it.

You can see all the formula 1, 2, and 3 results below.

RegexReplace to Replace the Last Occurrence of a String in Google Sheets

In some cases, you may want to replace the last occurrence of a whole word, I mean a string or you can say a text.

Example:

Text in cell A1 is;

John is a good student. John scored 89% mark

This formula =regexreplace(A1, "(.*)John", "$1He") replaces the sentence as below.

“John is a good student. He scored 89% mark”

Remove the Last Occurrence of a Word or Character in Google Sheets

It’s quite simple to remove the last occurrence of text if you follow the above regular expressions.

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

Use the above formula to remove the last occurrence of the string “present” in a sentence with a space character.

The value in cell A1 this time is He is present present.

Related Reading:

1. Extract Username from Email Address Using Regex in Google Sheets.
2. How to Use REGEXMATCH Function in Google Sheets.
3. How to Use Regexextract Function in Google Sheets and RE2 Regular Expressions.