Extracting specific numbers from imported data in Google Sheets

Other Questions Google docs
1

I’m working on a Google Sheets project where I need to pull out certain numbers from data I’ve brought in from another sheet. The data looks like this:

X1.text.3.text

I want to grab that second number (3 in this case). The pattern is always the same: letter-number-dot-word-dot-number-dot-word. The number I need could be one or two digits.

I thought I could use REGEXEXTRACT with IMPORTRANGE, but I’m running into two problems:

  1. My regex isn’t working. I tried this:
=REGEXEXTRACT(IMPORTRANGE("sheet_url", "SheetName!A2:A200"), "(\d+)(?!.*\d)")

Google Sheets says it’s not a valid regex. Any ideas how to fix this?

  1. The IMPORTRANGE isn’t pulling in all the data. It only gets the first cell, not the whole column.

Can anyone help me figure out what I’m doing wrong? I’m stuck and could really use some advice. Thanks!

2

Hey there! I’ve actually dealt with a similar issue before. Here’s what worked for me:

For extracting that second number, try this formula:

=REGEXEXTRACT(A1, “\d+.\w+.(\d+)”)

It looks for the pattern of number-dot-word-dot-number and grabs that last number.

As for the IMPORTRANGE problem, make sure you’ve authorized the connection between the two sheets. Sometimes it needs a manual approval the first time.

Also, instead of IMPORTRANGE, you could try copying the data into your current sheet first, then use the REGEXEXTRACT on that column. It might be easier to troubleshoot.

Hope this helps! Let me know if you need any clarification.

3

I’ve run into similar issues with regex in Sheets before. For your specific problem, try this formula:

=REGEXEXTRACT(A1, “[A-Z]\d+.[a-z]+.(\d+)”)

This expression targets a pattern with an uppercase letter followed by digits, a dot, lowercase letters, another dot, and then captures the number you need. Regarding IMPORTRANGE, please ensure that you have granted the necessary permissions between sheets. If it still imports only the first cell, consider validating the range or experimenting with a QUERY function against the imported data to fetch the entire column.

4

hey davidw, i’ve had simlar issues. try this formla: =REGEXEXTRACT(A1, “[A-Za-z]\d+.[a-z]+.(\d+)”) - it pulls the second num. also, ensure IMPORTRANGE has access between sheets.