Regex works in JavaScript but not Google Sheets

Question

I need a regex that can get the company name (in this case "google.com") from this format: company=google.com&extension

According to https://regex101.com/ this should work: (?<=company).*(?=&ext)

However when I try =REGEXEXTRACT(B3, "(?<=company).*(?=&ext)") in Google Sheets I get an error:

Function REGEXEXTRACT parameter 2 value "(?<=company).*(?=&ext)" is not a valid regular expression.

Ive also tried =REGEXEXTRACT(B3, "/(?<=company).*(?=&ext)/")

Function REGEXEXTRACT parameter 2 value "/(?<=company).*(?=&ext)/" is not a valid regular expression.

Artyom Vancyan · Accepted Answer · 2022-06-01 16:36:06Z

1

The REGEXEXTRACT function does not support lookarounds so you have to use non-capturing groups for parts that should not be captured and captured groups.

Just use (?:company)(.*)(?:&ext) instead of yours. Also, you were using , after B3. Do not know which version of Google Sheets you are using but it did not work for me and I had to change it to ;

=REGEXEXTRACT(B3; "(?:company)(.*)(?:&ext)")

NOTE: If you're using non-capturing groups then do not forget to use capturing group, otherwise it will return the whole matched values including non-capturing ones as well.

answered Jun 1, 2022 at 16:36

Artyom Vancyan

5,4023 gold badges17 silver badges38 bronze badges

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

2 Comments

Evanss Over a year ago

After I hit enter the ; gets replaced by a , and I also get this error: Error Function REGEXEXTRACT parameter 2 value "(?:company)(.*)(?:&ext)" does not match text of Function REGEXEXTRACT parameter 1 value

Artyom Vancyan Over a year ago

When I was using , it suggested replacing it with ;. I think it is just a version depending issue.

Gabriel Carballo · Accepted Answer · 2022-06-01 17:20:26Z

1

You could as well try the following:

=REGEXEXTRACT(B3,"=(\S*)?&")

This should extract only the specific string google.com or any word between the = sign and the & characters.

answered Jun 1, 2022 at 17:20

Gabriel Carballo

1,3531 gold badge6 silver badges9 bronze badges

1 Comment

Evanss Over a year ago

I had to change it to "=(\S*)?&e" and then it worked.

The fourth bird · Accepted Answer · 2022-06-01 20:13:33Z

1

You can match company and capture the part before the ampersand in a group:

company=([^\s&]+)&ext

See a regex demo.

answered Jun 1, 2022 at 20:13

The fourth bird

165k16 gold badges61 silver badges75 bronze badges

Comments

marikamitsos · Accepted Answer · 2022-06-02 02:11:55Z

0

One can also use the following

=REGEXEXTRACT(B2,"=(.*)&")

answered Jun 2, 2022 at 2:11

marikamitsos

10.6k23 silver badges29 bronze badges

1 Comment

Evanss Over a year ago

I have to use "=(.*)&e" but then it worked

Collectives™ on Stack Overflow

Regex works in JavaScript but not Google Sheets

4 Answers 4

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related