# Find an 8 digit stock number within a cell

I am looking to create a formula that enables me to search a set of data for an 8 digit number. I have attached my testing sheet.

In column B is the stock numbers I need to find within a set of data.

In column E is the full list to be checked against, however, within any one cell, you can have several different 8 digit stock numbers.

In my test sheet, I have found that a vlookup only searches for the first stock number and not all the stock numbers within that cell. I have attempted an index match formula combined with a transpose split however none of these has enabled me to properly search my data set.

` =ARRAYFORMULA(VLOOKUP(B2:B,\$E\$2:\$E,1,0))`

then

` =INDEX(\$E\$2:\$E\$1139,(MATCH(B2,TRANSPOSE(SPLIT(\$E\$2:\$E,";, ")))))`

The data can be separated within the cell with either spaces or "," or ";"

You should be able to make a copy of my test sheet using this link:

Hope you can help.

try:

``````=FILTER(E:E, REGEXMATCH(E:E&"", TEXTJOIN("|", 1, B2:B)))
``````

## update:

to get true/false use:

``````=INDEX(REGEXMATCH(E2:E&"", TEXTJOIN("|", 1, B2:B)))
``````

or if you want to return match or N/A use:

``````=INDEX(IF(REGEXMATCH(E2:E&"", TEXTJOIN("|", 1, B2:B)), E2:E, ISNA()))
``````