How to extract exact 10 digit ISBN10 in google sheets formula

user_4733894 0
Viewed 114

I have following data

ISBN: 9789380658797, 9380658796

How could I extract isbn10 only from the above string in google sheet using REGEXEXTRACT formula.

I have already tried below regex which is not working

1. (?<!\d)\d{10}(?!\d) -> works in regexr.com but not in google sheets. Function REGEXEXTRACT parameter 2 value "(?<!\d)\d{10}(?!\d)" is not a valid regular expression.

2. \d{10} -> return first 10 digit of isbn13
google-sheets regex
2 Answers

You may use

REGEXEXTRACT(A1, "(?:^|\D)(\d{10})(?:\D|$)")

See the regex demo

The point is that:

  • RE2 regex library does not support lookarounds, so your attempts with (?<!...) and (?!...) are invalid
  • You need to use a capturing group to extract that part only from the regex match, and in order not to match a non-digit or start/end of string, you need to use non-capturing groups, (?:...), here, (?:\D|^) and (?:\D|$).

enter image description here

user_38329700

This also works in your case! I used an approach that used boundary \b..\b to isolate match strings. if it isn't working, please upload more data:

=REGEXEXTRACT(A1,"\b\d{10}\b")

enter image description here

user_126917010
Related