regex from Javascript to google sheets-CodePudding

I'm trying to get this regex to google sheets. Wrap Text In JavaScript

In the solution

const wrap = (s) => s.replace(
    /(?![^\n]{1,32}$)([^\n]{1,32})\s/g, '$1\n'
);

I'm trying to replace this function with the native REGEXREPLACE to make it run faster. I have tried a few combinations but it all gives me the Function REGEXREPLACE parameter 2 value " /(?![^\n]{1,32}$)([^\n]{1,32})\s/g" is not a valid regular expression. with =REGEXREPLACE(AP18," /(?![^\n]{1,32}$)([^\n]{1,32})\s/g","$1\n") error.

I have also tried: =REGEXREPLACE(AP18,"(?![^\n]{1,32}$)([^\n]{1,32})\s","$1\n")

same result

Can I not move it to a native Google function? or is there something wrong with my regex?

CodePudding user response：

You have to use a quirky workaround when using REGEXREPLACE with newline in the replacement part: The "\n" newline does not work, so you have to enter a literal newline:

=REGEXREPLACE(D$7,"([^\n]{32}[^\s]{1,32})","$1
")

To do that, place the cursor to the right place in the replacement part, and hit CTRL ENTER.

Note that I don't use a lookahead. That is because built-in functions only support the RE2 regex syntax, not the full one of the V8 JavaScript engine.

Alternatively, create a custom function under Extension => Apps Script. Here are two variants:

function WRAP1(str) {
  return str.replace(/(?![^\n]{1,32}$)([^\n]{1,32})\s/g, '$1\n');
}

function WRAP2(str) {
  return str.replace(/([^\n]{32}[^\s]{1,16})/g, '$1\n');
}

I prefer the latter because it wraps a superSuperSuperSuperSuperSuperSuperSuperSuperSuperSuperSuperLongWordToo.

CodePudding user response：

The RE2 regex engine used by Google Sheets regex functions purposefully omits look-ahead and look-behind. That means that the (?!...) assertion is not supported by regexreplace().

It is usually possible to write an alternative regex that does not use those features. A full solution may involve several nested regex functions.

To learn the exact regular expression syntax used by the regex functions in Google Sheets, see RE2.

Note that the query() spreadsheet function uses Perl Compatible Regular Expressions that do support look-ahead and look-behind. Also note that Google Apps Script uses JavaScript regexes that support those assertions as well.