3

I'm trying to do a search for multiple strings in a cell with an OR-condition in Excel 2016.

E.g. I have a string abcd1234 and I want to find ab OR 12.

I'm using the german version where the function SEARCH is called SUCHEN and it should behave the same way.

I found this answer which suggests this solution:

SEARCH({"Gingrich","Obama","Romney"},C1).

I also found this website which suggests the same syntax:

SEARCH({"red","blue","green"},B5)

Same with this website:

SEARCH({"mt","msa","county","unemployment","|nsa|"},[@Tags])

So they basically say make a list of search terms separated by commas enclosed by curly braces and you're good.

But putting these into Excel 2016 just results in the usual meaningless Excel error message which says there was an error with the formula and it's always highlighting the whole part in curly braces.

Taking the first example the only way I could get Excel to not throw its error message was to change the syntax like this:

=SEARCH({"Gingrich";"Obama";"Romney"};C1)

But separating the search terms with semicolons doesn't apply the OR-condition correctly, so this is not the solution.

I'm aware from this answer that I could make separate searches and string them together with a condition, but I would like to avoid that, and I also want to know why the syntax that is supposed to work as confirmed by multiple sources is not working for me.

EDIT:

Okay, I'm starting to understand this, thanks to Solar Mike:

The code =IF(COUNT(SEARCH({"Romney","Obama","Gingrich"},A1)),1,"") works indeed perfectly fine.

Also =COUNT(SEARCH({"Romney","Obama","Gingrich"},A1)) works.

But =SEARCH({"Romney","Obama","Gingrich"},A1) does not.

Also =ISNUMBER(SEARCH({"Gingrich","Obama","Romney"},A1)) does not work.

I'd love to know the reason why.

Khris
  • 3,132
  • 3
  • 34
  • 54
  • It looks like you have your international settings set to use the semi-colon ";" as the separator and not the comma ",". You could change this if you wish. – Solar Mike Jul 23 '18 at 06:49
  • IMHO, from these links [MsHelp1](https://support.office.com/en-us/article/FIND-FINDB-functions-C7912941-AF2A-4BDF-A553-D0D89B0A0628) and [MsHelp2](https://support.office.com/de-de/article/SUCHEN-SUCHENB-Funktionen-9AB04538-0E55-4719-A72E-B6F54513B495) suchen() is find() in excel.. – p._phidot_ Jul 23 '18 at 07:13
  • @SolarMike: Thanks, changing the international settings solved that first problem. – Khris Jul 23 '18 at 08:05

2 Answers2

3

Ok, so this works:

OR(IFERROR(FIND("ab",A1,1),0),IFERROR(FIND("12",A1,1),0))

tested here : enter image description here

I followed one of the links and the version like this:

=IF(COUNT(SEARCH({"Romney","Obama","Gingrich"},C1)),1,"")

worked as expected for me, but if the search is isolated it then fails and I have not found an explanation ...

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
  • Thanks, it works fine now, though I also fail to understand the logic behind Excel's behaviour. – Khris Jul 23 '18 at 08:03
  • I can't see why search won't work separately - weird, but if the working solution gets the job done... :) – Solar Mike Jul 23 '18 at 08:07
2

Like other array-style formulas, the part that delivers the array has to be enclosed in some sort of aggregate function to make it scan through the array - otherwise it only looks at the first element of the array. So anything like COUNT, SUM, SUMPRODUCT will do the trick.

My preferred one is

=OR(ISNUMBER(SEARCH({"a","b","c"},A1)))

because you can easily change it to this if you want AND logic:

=AND(ISNUMBER(SEARCH({"a","b","c"},A1)))
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37