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:

https://docs.google.com/spreadsheets/d/1UckPZw6tWBw0H13p79N94IqV2EFyfhYNyEhmLecr3XM/copy#gid=55962883

Hope you can help.

1 answer

  • answered 2022-05-04 10:28 player0

    try:

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

    enter image description here


    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()))
    

    enter image description here

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum