CODEWITHSUNDEEP
excel
titusAdam
titusAdam

Reputation: 809

Excel Search Formula only works for exact match

I'm trying to use the search formula in Excel to check whether a column contains a string. However, I don't need the match to be exact. So f.i., if a cell in the column has abcd in it and while I'm searching for abc, I still want a value returned. Unfortunately at the moment I only get a value returned if I search for abcd exactly.

The formula I use right now looks like this:

  =SEARCH("abc";A2:A8)

There's no value returned, even though A3 has "abcd" in it.

Thanks in advance!

Upvotes: 3

Views: 8091

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Use MATCH() with a wildcard:

=MATCH("*abc*",A2:A8,0)

enter image description here

Upvotes: 2

durbnpoisn
durbnpoisn

Reputation: 4669

I normally don't like to provide a link for an answer... But I had to do this EXACT same thing a few weeks ago, and held on to the link that gave me the answer:

Contains in Excel

In short, it uses a method of checking a result being a number if the substring exists. Seems like a strange approach, but it works like a charm.

Upvotes: 5

Related Questions