regex from Javascript to google sheets

Question

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?

Yeah it seems that the regex is invalid for google sheets. No idea why though — evolutionxbox
– evolutionxbox, Commented Aug 15, 2022 at 17:58

Peter Thoeny · Accepted Answer · 2022-08-15 19:21:26Z

3

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.

edited Aug 15, 2022 at 19:21

answered Aug 15, 2022 at 18:56

Peter Thoeny

7,6381 gold badge16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jason Over a year ago

the functions are too slow... i get this loading... , the built in is instant. Thumbs up for that. thanks.

Peter Thoeny Over a year ago

@jason: I believe the initial slow loading is just to initialize. The function should run fast over thousands of uses.

doubleunary · Accepted Answer · 2022-08-15 19:33:48Z

1

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.

edited Aug 15, 2022 at 19:33

answered Aug 15, 2022 at 19:03

doubleunary

21.9k13 gold badges38 silver badges84 bronze badges

2 Comments

Peter Thoeny Over a year ago

Worth noting: The RE2 regex engine limitation only applies to built in functions. In custom scripts you can use the full regex supported by the V8 engine

doubleunary Over a year ago

Right. Apps Script is JavaScript and therefore has JavaScript regexes. The built-in query() function has look-ahead and look-behind as well. Edited the answer.

Collectives™ on Stack Overflow

regex from Javascript to google sheets

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related