Find Matching Values from Column

[Daedrix]

I'm working on a spreadsheet with two columns, Column A has a list of serial numbers & Column B has a list of scanned data which contains some of those serial numbers as well as junk data all in one continuous line (ex 'ABCD1234FGHJ' where '1234' would be the relevant serial number).

I'm trying to come up with a formula that compares a cell in Column B with all of the serials in Column A to see if any of the serial numbers matches the data in there, and if so writes that serial number in a cell. Any help with how to do so would be greatly appreciated.

 

[Joe4]

Is it possible to have more than one match?
If so, then what do you want to happen?
Is a VBA solution acceptable?

 

[Daedrix]

All the serial numbers should be unique so there shouldn't be more than one match per cell. I'm not familiar with VBA & I'll be using a work computer so I'm not certain if it will be a viable option.

 

[Joe4]

I think it might possibly have to involve an array formula, which is not my specialty. I do not know how you would search all those values, but only return the result to a single cell like that.
I will post my VBA solution, in case you can use it. Basically, it is just creating your own function to do what you want. You do not need to know VBA to use it, it i simply "plug and play".
So, you would just put this VBA code in a General Module in your workbook:

Function FindSerial(luVal As String, luRange As Range) As String
'   Lookup a value to see if it is found in a range

    Dim cell As Range
    
    Application.Volatile

    FindSerial = ""

    For Each cell In luRange
        If InStr(luVal, cell.Value) > 0 Then
            FindSerial = cell.Value
            Exit Function
        End If
    Next cell

End Function

Then you would just use it like any other function you do in Excel, where the structure is:
=FindSerial(string you are looking in, range of cells that contain the serial numbers)

So, if you were looking in the string in cell B1, and your serial numbers were in A1:A40, your formula would look like this:
=FindSerial(B1,$A$1:$A$40)
which you could then copy down for the rest of your values in column B.

If VBA does not work for you, hopefully someone else will provide a non-VBA solution.

 

[Daedrix]

Tried out the VBA code and it works perfectly! Thanks a bunch!

 

[Eric W]

Here's the formula version:

Book1
	A	B	C
1	Serials	Scanned	Extract
2	1234	ABCD1234FGHJ	1234
3	2345	OIY5555KJGH	5555
4	4444	314159asdf	314159
5	5555	;ljk1234asdf	1234
6	314159	Nothing	#N/A
Sheet10

Cell Formulas
Range	Formula
C2	=LOOKUP(2^16,FIND($A$2:$A$6,B2),$A$2:$A$6)

 

[Joe4]

Tried out the VBA code and it works perfectly! Thanks a bunch!

You are welcome!