Wildcard searching for multiple values and returning a result

[thhil]

Hi,

I have been trying to find a formula for this but I have yet to find one that works for me.

Data;

English - United Kingdom, Czech, Spanish
UK - United Kingdom
Swedish
English - US
French
US
France
German
Portuguese
+8000 more various values

I am looking for a formula that can search for multiple values e.g. english, us, uk and united and then return the value "Yes".

I was looking for something that includes wildcard searches or contains so that it is not affected by upper or lower case but combining this with multiple searches was proving difficult.

Many thanks in advance,

T

[Tony Valko]

One way...

Checks if the cell contains the string English.

=IF(COUNTIF(A2,"*English*"),"Yes","No")

[thhil]

One way...

Checks if the cell contains the string English.

=IF(COUNTIF(A2,"*English*"),"Yes","No")

Hi Tony,

Thanks for your reply, how could I also incorporate searching for "US" and "UK" or "United Kingdom" so that I can hopefully find all the localisations I am looking for with 1 formula?

T

[Tony Valko]

Do you mean you want to look for English OR US OR UK OR United Kingdom?

[thhil]

yes, if any of the value appear then they return the same result. because this original list is for the language of software but there are many variations of the values, so i want to find all the ones relating to english.

[Tony Valko]

Like this...

=IF(OR(ISNUMBER(SEARCH({"English","US","UK","United Kingdom"},A2))),"Yes","No")

[thhil]

Like this...

=IF(OR(ISNUMBER(SEARCH({"English","US","UK","United Kingdom"},A2))),"Yes","No")

This works perfectly, the addition of the "OR" at the beginning was what I was looking for!

Thank you very much

[Tony Valko]

You're welcome. Thanks for the feedback!