casmentor.blogg.se

It’s quite simple to remove the last occurrence of text if you follow the above regular expressions. He scored 89% mark” Remove the Last Occurrence of a Word or Character in Google Sheets This formula =regexreplace(A1, "(.*)John", "$1He") replaces the sentence as below. Text in cell A1 is John is a good student. 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. RegexReplace to Replace the Last Occurrence of a String in Google Sheets You can see all the formula 1, 2, and 3 results below. See the regular expression in the above formula to understand it. Since the Pipe symbol is a regular expression operator, we must use a backslash to escape it. If you want to replace the last occurrence of a | (pipe) symbol, then the regular expression will be as follows.įormula # 3: =regexreplace(A3, "(.*)\|", "$1 and") But in the last formula, I have used the string “, and” to replace the comma. Here in the first formula, I have replaced the last comma with the string “and”. If you want to get the expected result # 2, then the RegexReplace formula would be as below.įormula # 2: =regexreplace(A2, "(.*),", "$1, and ") To get the above-expected result # 1, use this RegexReplace formula.įormula # 1: =regexreplace(A1, "(.*),", "$1 and") Regular Expression to Replace the Last Occurrence of a Character in Google SheetsĪs mentioned above, we can use a REGEXREPLACE formula here. Here we can use Google Sheets REGEXREPLACE function. I want to replace the comma after “Nov” with the string “and” and the result should be as follows.Įxpected Result # 1: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov and DecĮxpected Result # 2: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, and Dec Value in cell A1: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

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

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