0

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

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.

3rd Nov 2022, 5:34 AM
Roshini Balivada
6 ответов
+ 2
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.
3rd Nov 2022, 2:54 PM
Tibor Santa
Tibor Santa - avatar
+ 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
3rd Nov 2022, 2:09 PM
Tibor Santa
Tibor Santa - avatar
+ 2
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...
3rd Nov 2022, 3:27 PM
Tibor Santa
Tibor Santa - avatar
+ 2
And if this is still not enough, you can write a VBA macro or script to do this search and populate the result...
3rd Nov 2022, 3:30 PM
Tibor Santa
Tibor Santa - avatar
0
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.
3rd Nov 2022, 2:30 PM
Roshini Balivada
0
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".
3rd Nov 2022, 3:15 PM
Roshini Balivada