CODEWITHSUNDEEP
regexgoogle-sheets
newbiew8572
newbiew8572

Reputation: 27

Extract Numbers After String Which Includes Quotes, Brackets, Braces in Google Sheets

There are numbers directly after the following string:

"balances":[{"balance":

Stuck with escaping the quotes and anything else. Nothing I tried worked unfortunately.

=REGEXEXTRACT(A1,"(?:"balances":[{"balance":)\K\d+")

Upvotes: 0

Views: 412

Answers (2)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626920

You should remember that

  • To input a double quote in a double quoted string literal in Google Sheets you need to double it
  • If you need to extract a part of a regex match with REGEXEXTRACT you need to use a capturing group to enclose that part with a pair of unescaped parentheses
  • If you do not need to quantify a sequence of patterns or use alternation there is no need to wrap that pattern part with a non-capturing group, just remove it.
  • Google Sheets regex engine is RE2 that does not support most Perl "extra" features, like \G, \K, (*SKIP)(*FAIL) and what not. If you want to test a regex online that should be RE2 compatible test it at http://regex101.com using Golang regex flavor.

Use

=REGEXEXTRACT(A1,"""balances"":\[{""balance"":(\d+)")

See the regex demo.

Upvotes: 1

newbiew8572
newbiew8572

Reputation: 27

=REGEXEXTRACT(A1,"(?:""balances"":\[{""balance"":)(\d+)")

Upvotes: 0

Related Questions