Using REGEXEXTRACT on an IMPORTRANGE in Google Docs

Question

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.

Answer 1

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

Answer 2

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.