CODEWITHSUNDEEP
excelvbasearchexcel-formula
Rushi
Rushi

Reputation: 35

Certain text containing string should return assigned value to that pattern

I want to display a value in a different column if the string contains some pattern in it. For ex: asd_wer1 is a string that contains _wer pattern in it and ewr_jer is the string that contains _jer pattern. So I want to display alan next to each string which contains _wer in it, and same for _jer also.

How can I do that?

I tried using below formula but it's not working =IF(OR(ISNUMBER(SEARCH("_wer",A1)),"Alan",IF(ISNUMBER(SEARCH("_jer",A1)),"Bob","")))

I've attached the pic below for more info.

enter image description here

enter image description here

Upvotes: 0

Views: 57

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

If one has the Dynamic Formula FILTER():

=@FILTER($E$1:$E$2,ISNUMBER(SEARCH($D$1:$D$2,A2)))

enter image description here

If not then use:

=INDEX(E:E,AGGREGATE(15,7,ROW($D$1:$D$2)/(ISNUMBER(SEARCH($D$1:$D$2,A2))),1))

enter image description here

Upvotes: 2

Related Questions