Find Replace with RegEx with Google Script

Question

I am trying to replace substrings that look like the following

Education|AAA|BBB|CCC|DDD|EEE

AAA|Educator|CCC|DDD|EEE

where Education or Educator could be in any position of the pipe delimited string. They are present at most only one time or zero times

I need to replace them with Educator/Education

What I want is

Educator/Education|AAA|BBB|CCC|DDD|EEE

AAA|Educator/Education|CCC|DDD|EEE

This works on the first pass with Education, I get

Educator/Education|AAA|BBB|CCC|DDD|EEE

AAA|Educator|CCC|DDD|EEE

But on the second pass replacing Educator, I get

Educator/Education/Education|AAA|BBB|CCC|DDD|EEE

AAA|Educator/Education|CCC|DDD|EEE

I think different a RegEx pattern for the ReplaceNth function would do it but cannot get what it should be

This is what I came up with in my limited ability with Google Script, which I got mostly from cobbling together from searches

Thanks

Edit: Updated with Wiktors solution given in a comment

function FRCrit_n(){
     FRCrit("Elements", "Combined-What Are The Primary Role(s) Being Play?"); 
 }

function FRCrit(shtName,cheader){
var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(shtName);
var data = sh.getDataRange().getValues();
var col =HTN(shtName,cheader)-1;  //Header to column index

  for(n=1;n<data.length;++n){
      data[n][col] = data[n][col].replace(/\bEducat(?:or|ion)\b/g, 'Educator/Education')[col],"Education","Educator/Education",1) 

      }

   sh.getRange(1,1,data.length,data[0].length).setValues(data);
 }

I don't know the language, but here is a pure regex which should work for matching: (^|\|)(Educator|Education)(\||$) — Tim Biegeleisen
Just use s = s.replace(/\bEducat(?:or|ion)\b/g, 'Educator/Education') — Wiktor Stribiżew

Aidan Aidan · Accepted Answer · 2018-05-25T14:06:28

In regular expression syntax, you can create an 'or' condition using a vertical bar to search for multiple things in one go. You can test this out in G Suite Sheets without needing any code.

I think you want to search for

Education|Educator

and replace it with

Education/Educator

When you are happy with your results you can put it back into code - the same regex syntax should work in javascript.

Find Replace with RegEx with Google Script

1 Answers