Isnumber Search Formula

[hrayani]

Hello All,

Here is the sample DATA in range (B2:B10)

[TABLE="width: 169"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Toyota[/TD]
[/TR]
[TR]
[TD]Suzuki[/TD]
[/TR]
[TR]
[TD]Suzuki[/TD]
[/TR]
[TR]
[TD]Yamaha[/TD]
[/TR]
[TR]
[TD]Toyota[/TD]
[/TR]
[TR]
[TD]Toyota[/TD]
[/TR]
[TR]
[TD]Suzuki[/TD]
[/TR]
[TR]
[TD]Ducati[/TD]
[/TR]
[TR]
[TD]Ducati[/TD]
[/TR]
</tbody>[/TABLE]

I want formula in Range (A2:A10)

I am using this formula in cell A2 to return 1 if the condition is met

[B]=IF(ISNUMBER(SEARCH($B$1,B2)),1,0)[/B]

All I want is to search if multiple entries are there in cell B1

For example To, Duca


Thanks in Advance

Regards,

Humayun

 

[JamesonMH]

This will tell you how many occurrences of Toyota and Ducati are in your range (5). Is that what you're looking for?

=SUM(--ISNUMBER(SEARCH(B8,A1:A8))+ISNUMBER(SEARCH(B9,A1:A8)))
Ctrl+Alt+Enter

James

 

[hrayani]

Hi James Thanks for the reply

I am not looking for number of occurences.

All I want is if I enter (To, Duc) & if the cell contains Toyota or Ducati then it should return 1 as an answer otherwise 0

 

[Joe4]

How about just:

=ISNUMBER(SEARCH("To",B2)) + ISNUMBER(SEARCH("Duca",B2))

and copy down for all rows.

You can replace "To" and "Duca" with cell references, if you like.

 

[hrayani]

Thanks Joe But it is not working coz both criterias are in the same cell i.e. B1 - and also in this format To, Duc (With comma)

Had it been two different cells like "To" in B1 & "Duc" in C1 then your formula would have work perfect

 

[Joe4]

That's really not a great format to use.
Are there always going to be exactly two entries in the cell?
Might there be one or three?

 

[hrayani]

There can be one, two, three - multiple entries even more then three

Single entry will be like To
Double entry will be like To, Duc
Tripple entry will be like To, Duc, Suz

 

[Joe4]

With that structure, I cannot think of a good way of doing it without VBA.
You basically need a way to split all the entries, and loop through them. I do not know of a way to do that in a native Excel formula.
I would probably create a User Defined Function in VBA and use that.

Maybe someone else will have some other ideas...

 

[Fluff]

Wit a UDF if you're interested

Function hrayani(Cl As Range, Crit As Range) As Long
   Dim Sp As Variant
   Dim i As Long
   Sp = Split(Crit, ",")
   hrayani = 0
   For i = 0 To UBound(Sp)
      If InStr(1, Cl, Trim(Sp(i)), vbTextCompare) > 0 Then
         hrayani = 1
         Exit Function
      End If
   Next i
End Function

Used like
=hrayani(B2,$B$1)

 

[hrayani]

Hi Fluff,

Thanks working great. PERFECT

Just one thing if the criteria cell has nothing but a single space even then the answer is showing 1 whereas it should show zero

Only if there is a value with space in the data range like "Humayun & Fluff" and if a space is there in the criteria cell then is should show 1 otherwise not