Home Check if string contains a substring from a list Excel VBA
Reply: 1

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#
tigeravatar Reply to 2017-12-07 14:24:15Z

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)
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.782311 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO