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

Published on

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.

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.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

How to Sort Pie Slices in Google Sheets

To sort pie slices in a pie chart, you need to sort the data...

Filter Items Unique to Groups in Google Sheets

In this tutorial, we'll learn how to filter items unique to groups in Google...

Find Common Items Across Multiple Columns in Google Sheets

This tutorial explains how to find common items across multiple columns in Google Sheets....

Sort Column by Length of Text in Google Sheets

To sort a column by length of text, you can either use the QUERY...

More like this

How to Sort Pie Slices in Google Sheets

To sort pie slices in a pie chart, you need to sort the data...

Filter Items Unique to Groups in Google Sheets

In this tutorial, we'll learn how to filter items unique to groups in Google...

Find Common Items Across Multiple Columns in Google Sheets

This tutorial explains how to find common items across multiple columns in Google Sheets....

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.