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 (\|
).

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.
Related Reading
- How to Remove Extra Delimiters in Google Sheets
- Substitute Nth Match of a Delimiter from the End of a String in Google Sheets
- How to Replace Multiple Comma-Separated Values in Google Sheets
- How to Replace Commas within or outside Brackets in Google Sheets – Regex
- How to Replace Every Nth Delimiter in Google Sheets
- Remove or Replace Last Character from a String in Google Sheets