Using REGEXEXTRACT on an IMPORTRANGE in Google Docs

user_18636933 0
Viewed 77

I am importing a range from another Google sheet and I need to pull a specific number from the data that is imported. The data looks something like:

R2.word.4.word

I want to extract the second number. It will always follow this format (a letter and a number then a period then a word then a period then a number (might be single or double digit) then a period and a word). The regex to extract the second number should be: (\d+)(?!.*\d) and I have tested it in multiple regex test sites. However, Google docs gives me an error stating it is not a regular expression. I tried something like this (edited out URL and the sheet name):

=REGEXEXTRACT(IMPORTRANGE(URL,Sheet!A2:A200), "(\d+)(?!.*\d"))

Can anyone help me understand how I can fix this?

And the other issue here is that it isn't actually importing the range. I only get it to import on the first cell and not down the column.

google-sheets-formula google-docs regex
3 Answers

You could write a pattern like:

=REGEXEXTRACT(A2,"^[A-Z]\d+\.\w+\.(\d+)")

Explanation

  • ^ Start of string
  • [A-Z] Match a single uppercase char
  • \d+ Match 1+ digits
  • \. Match a dot
  • \w+ Match 1+ word characters
  • \. Match a dot
  • (\d+) Capture group 1, match 1+ digits

Regex demo

enter image description here

user_54249880

With your shown samples please try following regex.

=REGEXEXTRACT(A2,"^[a-zA-Z]\d+\.[^.]*\.(\d+)\.\S+$")

Here is the Online demo for above regex.

Explanation: Adding detailed explanation for above regex.

^[a-zA-Z]  ##From starting of value matching a-zA-Z here.
\d+        ##Matching 1 or more occurrences of digits.
\.[^.]*\.  ##Matching literal dot till next occurrence of dot here.
(\d+)      ##Creating 1 capturing group and which has 1 or more digits matching in it.
\.\S+$     ##Matching literal dot followed by 1o or more non-spaces till end of value.
user_58665800

"It will always follow this format"

Based on the above; you can use REGEXEXTRACT() but it's slow compared to simple SPLIT() which in your standardized format is ideal:

enter image description here

Formula in B2:

=INDEX(SPLIT(A2:A3,"."),0,3)

This is an array-formula by default and will spill all values down. Just apply it to your entire range.

user_97581940
Related