Do you want to replace the last occurrence of a character in Google Sheets? Well, you’re in luck! With a simple Regular Expression (Regex) function, you can easily achieve this. And hey, guess what? You can also use the same formula to extract the last occurrence of a word. Cool, right?
Sometimes, you may find yourself in a situation where you need to replace the last occurrence of a character, such as a colon, semicolon, comma, or even a pipe, with another character. Let’s take an example to better illustrate this.
Let’s say we have the following value in cell A1: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec. And we want to replace the comma after “Nov” with the string “and”. The expected result should be:
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 can we achieve this in Google Sheets? It’s quite simple! We can leverage the power of the REGEXREPLACE function. Here’s how:
Regular Expression to Replace the Last Occurrence of a Character in Google Sheets
To get the expected result #1, use the following formula:
=regexreplace(A1, "(.*),", "$1 and")And if you want to get the expected result #2, use this formula instead:
=regexreplace(A2, "(.*),", "$1, and ")In the first formula, we replace the last comma with the string “and”. But in the second formula, we use the string “, and” to replace the comma.
But what if you want to replace the last occurrence of a pipe symbol (|) with a string? No worries, we got you covered!
Replace Pipe Symbol with a String
If you want to replace the last occurrence of a pipe symbol, the regular expression will be as follows:
=regexreplace(A3, "(.*)|", "$1 and")Since the pipe symbol is a regular expression operator, we must use a backslash to escape it. Pretty neat, huh?
You can see all the results of the formulas mentioned above in the image below:
RegexReplace to Replace the Last Occurrence of a String in Google Sheets
Now, let’s take it a step further. In some cases, you may want to replace the last occurrence of a whole word or a string. For example, let’s say we have the following text in cell A1:
John is a good student. John scored 89% mark.
We can use the following formula to replace the last occurrence of the word “John” with “He”:
=regexreplace(A1, "(.*)John", "$1He")The result would be:
“John is a good student. He scored 89% mark.”
Pretty cool, right?
Remove the Last Occurrence of a Word or Character in Google Sheets
Guess what? Removing the last occurrence of text is just as simple as replacing it. All you need to do is follow the same regular expressions we discussed earlier. For example, if you want to remove the last occurrence of the word “present” in a sentence with a space character, use the following formula:
=regexreplace(A1, "(.*)present", "$1")Let’s say the value in cell A1 this time is “He is present present.” Using the formula above, the result would be:
“He is present “
And there you have it! Now you know how to replace or remove the last occurrence of a character or word in Google Sheets using regular expressions. Pretty handy, isn’t it?
If you want to dive deeper into regex in Google Sheets or explore other useful functions, be sure to check out Crawlan.com. It’s your go-to resource for all things Google Sheets and more!
Happy spreadsheet-ing, my friend!
