REGEX possible?

0
votes

sample here

input: ~|AAA|BBB|~|~|~|BBB|AAA|~|~|CCC|DDD|AAA|~|AAA|~|EEE|~

output: @AAA|BBB@@@@BBB|AAA@@@CCC|DDD|AAA@@AAA@@EEE@

Using Google-Sheets REGEX functions - is it possible to get the output from input ?

The rules are :

  1. any "|" adjacent to "~" should become a "@"
  2. all "~" disappear
  3. ignore the 3 letter words (which are variable length alpha numeric strings)

I have done this using 3 SUBSTITUTE statements but this bloats my already big formula the string literal arguments are error prone. So can this be done with a REGEX function?

1
google-sheets

1 Answers

1
votes

Because 'lookarounds' are not supported in RE2 (the regex engine used by google sheets), I'm afraid you'll need at least two functions to get the desired result.

See if this works:

=substitute(regexreplace(A1, "~\||\|~", "@"), "@|", "@@")