CODEWITHSUNDEEP
excel
NZ_DJ
NZ_DJ

Reputation: 341

Find exact words in a string for a range of words in excel

I am trying to find whether a string contains a range of 'exact words' in excel. eg:

A                                    B          C           D              E (Output)
Apple is a large company            App        IOS      App Store           Software

If a string under column "A" contains any of the EXACT words on column B:D, the output should be column "E".

I currently have a formula that looks like:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(B1:D1,A1)))>0,E1,"")

The issue is that it doesn't search for EXACT words; so "App" would be picked up by "Apple".

I could amend the SEARCH so that each of the columns B,C,D are searched separately, but was wondering if there was an easier way to do this?

Thanks

Upvotes: 0

Views: 500

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

add spaced to the front and back:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(" "&B1:D1&" "," "&A1&" ")))>0,E1,"")

enter image description here

Upvotes: 4

Related Questions