I’m working with Google Sheets and trying to pull specific numbers from data that comes from another spreadsheet using IMPORTRANGE. The imported data has a consistent pattern like this:
T5.example.7.sample
I need to get the last number from each entry. The format is always the same - it starts with a letter and digit, then a dot, then text, then a dot, then one or two digits, then a dot and more text.
I created a regex pattern (\d+)(?!.*\d) that works fine when I test it online, but Google Sheets throws an error saying it’s not valid. Here’s what I’m trying:
The formula doesn’t work and I’m getting regex errors. Also, the IMPORTRANGE only seems to work for the first cell instead of the whole column range I specified.
Has anyone dealt with similar issues? I’m not sure if the problem is with my regex syntax or how I’m combining these functions together.
hey tom, looks like you got a couple things mixed up. IMPORTRANGE gives you a range, but REGEXEXTRACT can’t work on that directly. try wrapping it in ARRAYFORMULA: =ARRAYFORMULA(REGEXEXTRACT(IMPORTRANGE("url","range"),"\.(\d+)\..*$")). also, remember to escape dots in regex, use \. for that!
Had this exact headache a few months back with imported data from multiple sheets. The issue isn’t just your regex - it’s how Google Sheets handles IMPORTRANGE with other functions. Get your IMPORTRANGE working by itself first, then add the extraction. For your pattern, \.([0-9]{1,2})\. works way better than lookaheads since Google Sheets’ regex engine hates them. Escape those dots properly and keep it simple. Once your import’s stable, wrap it in ARRAYFORMULA like others said. Pro tip: test your regex on one cell first with a direct reference instead of the full range - saves you tons of debugging time.
Google Sheets handles negative lookaheads weird compared to regular regex engines. Skip the (?!.*\d) approach and match the pattern directly instead. Since your format’s consistent, use \.([0-9]+)\.sample$ to grab the digits before the final dot and “sample”. This assumes “sample” is always at the end. If the ending text changes, try \.([0-9]+)\.[^.]*$ instead. Make sure you’re escaping the literal dots with double backslashes in Google Sheets. Also, authorize your IMPORTRANGE formula first - run it separately before combining with REGEXEXTRACT. Google Sheets’ regex parser gets cranky with complex lookaheads. Simple patterns work way better.