Home Check if string contains a substring from a list Excel VBA

# Check if string contains a substring from a list Excel VBA

xLokos
1#
xLokos Published in 2017-12-07 13:40:23Z
 I have cells that contain account numbers in column A and strings that contain text and account numbers in column B. [Data example] I would like to create a list of accounts and then check if any of those accounts is contained in column B. If it is I want to extract this account number to column C (in the same row it was found). I am a VBA noob so I'm not sure how this could be done. I asked a similar but much more complex question earlier this week, but this should be easier to "solve". [This is how I would like it to look like after processing] There might be other numbers with the same length as the account numbers in column B that are NOT account numbers, so this excludes some solutions.
tigeravatar
2#
 In cell C1, use this formula and copy down: =IF(A1="",INDEX($A$1:$A$16,MATCH(1,INDEX(COUNTIF(B1,"*"&$A$1:$A$16&"*")*($A$1:$A$16<>""),),0)),A1)