CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formula
Dorczenzo
Dorczenzo

Reputation: 31

Regex expression replacing many "$" with different items in Google Sheets

I did not find any answers regarding my problem. Maybe I am just not so familiar with regex expressions.

I have the following cell in Google Sheets:

$ goes $, and $ goes $.

I would like to replace those dollar signs with different items. Let's say I imagine it somehow like that:

=REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(A1; "\$"; "John");"\$";"left");"\$";"Bill");"\$";"right")

.. but of course, it does not work as I would like, giving the results like:

John goes John, and John goes John.

I wonder if I am able, just with some formula, to make it like this:

John goes left, and Bill goes right.

Thanks!

Upvotes: 3

Views: 429

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627317

You can use

=REGEXREPLACE(A1; "\$([^$]*)\$([^$]*)\$([^$]*)\$", "John$1left$2Bill$3right")

See the regex demo.

If you need to replace the first occurrence, you can use

=REGEXREPLACE(A1; "^([^$]*)\$([^$]*)\$([^$]*)\$([^$]*)\$", "$1John$2left$3Bill$4right")

That is, you need to match and capture the parts between $ chars and then use backreferences to these group values in the replacement pattern.

Regex details:

  • ^ - start of string
  • ([^$]*) - Group 1: any zero or more chars other than $
  • \$ - a $
  • ([^$]*)\$([^$]*)\$([^$]*)\$ - Group 2 with zero or more non-$s, a $, Group 3 with zero or more chars other than $ and again a $ and Group 4 with zero or more non-$s and the last $.

Upvotes: 4

Related Questions