How to find value of one column as a substring in another column value through excel formula?

Question

If column a had value "123", entire column b has values in this format "445667: 456, 123". How to find all the column b values which contains column a value as substring.

Answer 1

The Learner ok I see. In that case you can adjust the formula like this:
=NOT(ISERR(FIND(A1, TEXTJOIN("|", TRUE, B:B))))
here the TEXTJOIN function concatenates all nonempty cells in the B column, using separator specified.

Answer 2

=NOT(ISERR(FIND(A1, B1)))
use this formula in column C and it shows true when column B contains the substring of column A

Answer 3

In that case, one more idea is that you could extract the part of the data which must be searched (maybe the part after the comma) into a separate column, and then do a VLOOKUP on that. But I can't say for sure if this is viable, without seeing the shape and variations in your data...

Answer 4

And if this is still not enough, you can write a VBA macro or script to do this search and populate the result...

Answer 5

Tibor Santa Thankyou for your answer, but my scenario was to find if column A value is present as substring in any of the row values of column b.

Answer 6

Tibor Santa Thankyou for your response. your formula works good. but my data has 1200+ rows in column b.so it's throwing some error like "function returned text was longer than maximum support length".