Hey everyone! I’m struggling with a Google Sheets problem. I’m trying to get data from another sheet and then pull out certain numbers from it. The data looks like this:
L5.text.8.text
I want to grab that second number (8 in this case). The format is always the same: letter, number, dot, word, dot, number, dot, word. I thought I could use REGEXEXTRACT with IMPORTRANGE, but it’s not working right. Here’s what I tried:
=REGEXEXTRACT(IMPORTRANGE("sheet_id", "Sheet1!A2:A200"), "(\d+)(?!.*\d)")
But I’m getting an error saying it’s not a valid regex. Also, the IMPORTRANGE part only seems to work for the first cell, not the whole column. Any ideas on how to fix this? I’m totally stuck!
hey swimmingshark, i think i got a solution for ya. Try this formula:
=ARRAYFORMULA(REGEXEXTRACT(IMPORTRANGE(“sheet_id”, “Sheet1!A2:A200”), “\d+.\w+.(\d+)”))
it should grab that second number from all the cells at once. lemme know if it works!
I’ve encountered a similar issue before. Instead of using REGEXEXTRACT with IMPORTRANGE directly, try splitting the process into two steps. First, use IMPORTRANGE to fetch the data into a separate column. Then, apply your regex formula to that column.
For the regex part, this pattern should work better:
=REGEXEXTRACT(A1, “\d+.\w+.(\d+)”)
This looks for any digits, followed by a dot, then any word characters, another dot, and finally captures the number you want.
To apply it to multiple cells, you can use an ArrayFormula:
=ArrayFormula(REGEXEXTRACT(A1:A, “\d+.\w+.(\d+)”))
Hope this helps solve your problem!
I’ve dealt with similar data extraction challenges before, and here’s what worked for me:
Instead of combining IMPORTRANGE and REGEXEXTRACT in one formula, I’d suggest a two-step approach. First, import your data into a separate column or sheet using IMPORTRANGE. Then, apply the regex to that imported data.
For the regex part, this pattern should do the trick:
=REGEXEXTRACT(A1, “L\d+.\w+.(\d+)”)
This specifically looks for the ‘L’ at the start, followed by numbers, a dot, any word, another dot, and then captures the number you’re after.
To apply it to the whole column, wrap it in an ARRAYFORMULA:
=ARRAYFORMULA(REGEXEXTRACT(A1:A, “L\d+.\w+.(\d+)”))
This approach has always been more reliable for me when dealing with complex data structures in Google Sheets. Give it a shot and see if it resolves your issue.