Regex works in JavaScript but not Google Sheets

user_467875 0
Viewed 65

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.

google-sheets regex
4 Answers

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.

user_127551870

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.

user_173900820

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

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

See a regex demo.

enter image description here

user_54249880

One can also use the following

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

enter image description here

user_15277800
Related