I’m facing challenges while trying to extract particular numbers from data I imported using IMPORTRANGE and REGEXEXTRACT.
The data that I’m importing looks like this:
D5.example.3.item
I want to retrieve the last numeric part. It consistently has this format: letter+number+dot+word+dot+number+dot+word. My goal is to extract that last number, which can be either one or two digits long.
I’ve been using the regex pattern: (\d+)(?!.*\d) which tests well on different regex websites. However, when I try it in Google Sheets, it gives me an error claiming it’s not a valid regular expression.
Here’s the formula I’ve been attempting:
=REGEXEXTRACT(IMPORTRANGE("spreadsheet_link","DataSheet!B2:B150"), "(\d+)(?!.*\d)")
I have two issues at hand. First, it seems that Google Sheets doesn’t accept my regex pattern, even though it functions perfectly elsewhere. Second, the IMPORTRANGE only fills the first cell with data, not extending down the column as I need it to.
Can anyone shed some light on what might be going wrong here? Is there an alternative way to write this regex that Google Sheets might accept?
google sheets regex is kinda limited compared to other tools. try using (\d+)$ instead - the dollar sign matches end of string which should grab your last number. also importrange returns an array so you might need to wrap it with arrayformula to fill down the column properly.
Had this exact problem when working with product codes in my inventory tracking sheet. The regex engine in Google Sheets is quite restrictive and your negative lookahead syntax simply isn’t supported. What worked for me was using =ARRAYFORMULA(IF(IMPORTRANGE("spreadsheet_link","DataSheet!B2:B150")<>"",REGEXEXTRACT(IMPORTRANGE("spreadsheet_link","DataSheet!B2:B150"),"(\d+)$"),"")) which grabs the final digits at the end of each string. The IF condition prevents errors on empty cells and ARRAYFORMULA processes the entire range at once. You might also want to consider using REGEX with a simpler pattern like [^\d]*(\d+)[^\d]*$ if your data structure varies. Double check that your importrange has proper permissions too since that can cause unexpected behavior with array functions.
The issue stems from Google Sheets using RE2 regular expressions which don’t support negative lookaheads like (?!.*\d). Your pattern won’t work because of this limitation. Instead of fighting with complex regex, consider using SPLIT function which handles this scenario more reliably. Try =ARRAYFORMULA(REGEXEXTRACT(IMPORTRANGE("spreadsheet_link","DataSheet!B2:B150"),"\.(\d+)$")) - this extracts digits after the last dot. The ARRAYFORMULA wrapper ensures the formula applies to the entire imported range rather than just the first cell. I’ve used this approach successfully when dealing with structured data imports where the last segment needed extraction.