CODEWITHSUNDEEP
regexspreadsheetexpression
user1373858
user1373858

Reputation: 21

Why won't this regexp work in google spreadsheets?

I'm trying to extract from a url using a regexp in google spreadsheets. However the spreadsheet returns #VALUE! with the following error: Invalid regular expression: invalid perl operator: (?<

Here is the regexp I'm using: (?<=raid_boss=)[a-zA-Z0-9_]+

A sample url will contain a variable in it that says raid_boss=name. This regexp should extract name. It works in my testing program, but not in google spreadsheet.

Here is the exact contents of the cell in google spreadsheets: =REGEXEXTRACT( B1 ; "/(?<=raid_boss=)[-a-zA-{}-9_]+" )

Any insight or help would be much appreciated, thank you!

Upvotes: 2

Views: 6655

Answers (2)

benfromredant
benfromredant

Reputation: 181

Javascript is not the issue - Google Sheets uses RE2 which lacks lookbehind along with other useful things.

You could use:

regexextract(B1, ".*raid_boss=(.*)")

or else native sheet functions like FIND, SUBSTITUTE if that isn't working

Finding a good regex testing tool is tricky - for example you can make something that works in http://rubular.com/ but fails in GSheets. You need to make sure your tool supports the RE2 flavour eg: https://regoio.herokuapp.com/

Upvotes: 0

Mark Reed
Mark Reed

Reputation: 95252

Sounds like whatever regular-expression engine Google Docs is using doesn't support lookbehind assertions. They are a relatively rare feature.

But if you use captures, REGEXEXTRACT will return the captured text, so you can do it that way:

=REGEXEXTRACT( B1 ; "raid_boss=([a-zA-Z0-9_]+)" )

Upvotes: 2

Related Questions