EXCEL FORMULAL- Searching for multiple sub-strings in a main string

[Harry001]

Hi,
I have test data as below. I want to search col.B sub_string in col.A String, if the substring is present in col.A then return the sub_string value. Can anyone suggest an excel formula/solution for this problem.


Thanks,
Harry

 

[Khalid NGO]

Hi Harry, welcome to the forum

Can you please post the excel file?

Regards,

 

[Khalid NGO]

Ok now, I have made the sample, and as to your sample data you can use the simple:
=MID(A2,SEARCH("Name?",A2),5)

But I am sure this is not you're looking for.
You can use this in C2 (if I am on right track) :

=IFERROR(MID(A2,IF(LEN(B2)>0,SEARCH(B2,A2),0),LEN(B2)),MID(A2,FIND(":",A2)+1,(FIND(";",A2)-FIND(":",A2))-1))

See the attached.

Regards,

 

[Harry001]

Hi Khalid,
Thanks for the formula. It works.
Can we extend this formula for the huge datasets. Let's say Col.A -1000 rows to search and Col.B 40 different sub_strings.
& Is there a way to use Combination of VLOOKUP, SEARCH and FIND.
Please let me know.

Thanks
Harsha.

 

[Deepak]

@ Harry001
Based on the @Khalid NGO attachment.

=IF(COUNTIF($B$2:$B$5,MID(A2,FIND(":",A2)+1,(FIND(";",A2)-FIND(":",A2))-1)),MID(A2,FIND(":",A2)+1,(FIND(";",A2)-FIND(":",A2))-1),"")

 

[Deepak]

A little sorter!

=IFERROR(INDEX($B$2:$B$6,MATCH(MID(A2,FIND(":",A2)+1,(FIND(";",A2)-FIND(":",A2))-1),$B$2:$B$6,0)),"")

 

[Deepak]

Oh! How i missed it.

=IFERROR(VLOOKUP(MID(A2,FIND(":",A2)+1,(FIND(";",A2)-FIND(":",A2))-1),$B$2:$B$7,1,0),"")

 

[Khalid NGO]

Hi Khalid,
Thanks for the formula. It works.
Can we extend this formula for the huge datasets. Let's say Col.A -1000 rows to search and Col.B 40 different sub_strings.
& Is there a way to use Combination of VLOOKUP, SEARCH and FIND.
Please let me know.

Thanks
Harsha.

Hi Harsha,
Good day...

Not sure how your real data is looks like, I will again ask you to post the sample file, if there is sensitive data, replace it with fake; and mention your expected result manually.

Meanwhile check the various posted solution.

Regards,

 

[r1c1]

Good formulas @Deepak

Here is one more that is generic.

Assuming your substrings are in E3:E6 and the cell to match is B3,


=INDEX($E$3:$E$6,SUM(COUNTIFS(B3,"*"&$E$3:$E$6&"*")*ROW($A$1:$A$4)))

Array formula, so press CTRL+Shift+Enter after typing.

Here is the sample workbook.

A while ago we had a homework problem exactly like this. See: http://chandoo.org/wp/2012/12/14/find-text-pattern/

 

[Harry001]

Thank you all.
This is exactly what I wanted. Every formula works.
@Khalid NGO: sorry about the sample data. Next time I will post it.

Thank you.