REGEX.EXTRACT - The range … can’t be used as a single value.

REGEX.EXTRACT is simple in theory but the codes needed to extract exactly what you want can be fairly complex.

REGEX.EXTRACT extracts substrings from a longer string. It is a text function. In order to use it to retrieve data from a range of cells, you first need to turn that range of cells into one long string. Then you can extract them one by one. Here is one possible solution:

=1*REGEX.EXTRACT("~"&TEXTJOIN("~",TRUE,Table 1::I$5:I$158)&"~","(?<=~)[0-9,.-]+(?=~)",ROW(cell)−1)

TEXTJOIN concatenates all the cells in that range into one long string with a delimiter between each. I used ~ as the delimiter and I tacked a ~ onto each end of the string so all cells have a ~ on both sides. The TRUE means blank cells are ignored.

\d is the numbers 0-9. If those are the only characters in your numbers then you can use \d+ instead of what I did. I included comma, decimal point, and negative sign as allowable characters.

The code "(?<=~)[0-9,.-]+(?=~)" is looking for a string of the characters 0-9,.- with a ~ on both sides, but not including those ~ characters.

The results from REGEX.EXTRACT are strings, not actual "numbers". I multiplied by 1 to turn them into actual numbers.

Note that a string like 90-34,,56.,7 will be extracted as if it was a number but will end up being an error when it is multiplied by 1.

I think every other solution in Numbers requires an extra column to determine (or keep track of) the rows that match your criteria. If the REGEX.EXTRACT approach works for you, use it. If you want another solution, I'm sure we can make you one. I do not know the limits or the speed of computation of using TEXTJOIN and REGEX.EXTRACT together. I doubt it could do a million rows of data, maybe not even tens of thousands, but it works well enough for smaller tables.

One thing I thought of this morning is that if your cells contain numbers or nothing at all (no other text or data, just numbers or nothing), the criteria doesn't need to be [0-9.,-]+ it can be [^~]+ which means anything not a ~.

=1*REGEX.EXTRACT("~"&TEXTJOIN("~",TRUE,Table 1::I$5:I$158)&"~","(?<=~)[^~]+(?=~)",ROW(cell)−1)

One problem I have with REGEX.EXTRACT for numeric data is that it is text-based. For text it is perfectly fine and can do some very complex matching. For numbers and dates and things like that you have to be careful, trying to make sure it will match all your numbers and not something else unintended, and you can't do numeric comparisons (like searching for numbers greater than 5 or dates greater than Jan 1, 2000). Also, and of high importance, the results are the text you see in the cell. If the value in the cell is 100.123 but the cell is formatted to one decimal as 100.1 then the result from REGEX.EXTRACT will be 100.1 exactly, it will not be 100.123.

Search the internet for help on regular expressions. I didn't find it easy to learn. I still don't when it gets complex. Half the "solutions" you find online are wrong or don't actually work, but enough of them do.

I think you need to be clearer about what you're trying to do with this data.

Ostensibly, I was able to get your REGEX.EXTRACT to work by amending the parameters. Even though the third parameter says it returns the match-occurrence, I found that using the fourth parameter (capture-group-occurrence) actually works.

=REGEX.EXTRACT(L 4, "\d+",0,ROW()-1)

Maybe I'm too fuzzy about regular expressions to be able to decipher this one.