Google Sheets - extract numbers before/after or between specific text or characters-CodePudding

I need a formula that extracts numbers only from a cell which includes these numbers and other characters.
I've tried several different formulas, but they yield different results.
The problem is that I can only get the values I want if the number is first in the list. If it's between different characters I also get what comes before the value I need to be extracted.
I've created a sheet with some basic samples just to get an idea of what I want to achieve and what I've tried so far.

CodePudding user response：

You can change your basic formula to this simple one for each column

=INDEX(IF(A2:A7="",, 
   IF(REGEXMATCH(A2:A7,"dogs"), REGEXEXTRACT(A2:A7,"(\d )dogs"),0)))

(Do adjust the formula according to your ranges and delete everything under it)

CodePudding user response：

For dynamic spill array you may try below formula-

=BYROW($A$2:$A,LAMBDA(x,IF(x="",,IFERROR(REGEXEXTRACT(QUERY(FLATTEN(SPLIT(x,"/")),"select * where Col1 like '%" & LOWER(H$1) & "%'"),"\d "),0))))

See you workbook sheet name harun24hr.

Edit: Based on formula from @marikamitsos answer you can use-

=BYROW($A$2:$A,LAMBDA(x,IFERROR(IF(x="",,REGEXEXTRACT(x,"(\d )"&LOWER(K$1))),0)))