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.

CodePudding user response：

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

CodePudding user response：

With your shown samples please try following regex.

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

Here is the

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.