CODEWITHSUNDEEP
excelexcel-formula
Muuraharkka
Muuraharkka

Reputation: 25

How to find multiple starting letters in a column?

I am trying to research multiple starting references in a list of Ids and set a cell as "yes" if the ID contains the wanted STARTING letters.

This is what I have for now:

=IF(COUNT(FIND({"AUD","AZ","BURD"},$D2)),"YES","")

However this returns me yes if the cell contains the letters, but I want it only for starting letters

so I want AUDRTY to be "YES" but RTAUDFR to not return anything

I have found a solution with IF(LEFT("AUD",3),"YES","") however the letters I look for don't always have the same size.

Thank you in advance for your answer!

Upvotes: 2

Views: 138

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Use COUNTIF() with wildcards:

=IF(SUMPRODUCT(COUNTIF($D2,{"AUD*","AZ*","BURD*"})),"YES","")

If any are present at the beginning it will return 1 to the criteria, which IF will see as True and return YES. Otherwise it will return 0 and the IF will return an empty string.

Upvotes: 3

Related Questions