Excel: IsNumber(search) - want to use wildcard to match any text 2

[wz]

I want to use a wildcard (?) in an isnumber statment and it is returning an error.

cell: a1 = SEWM
cell: a2 = isnumber(search("SW",a1)

results in false (because SW are not next to each other in cell a1. Help screens suggest I can use wildcard (?) to show any sequence in search text - doesn't seem to work. Tried =isnumber(search(?"SW",a1)

 

[xlbo]

You will need to use something like:
=IF(ISERROR(SEARCH("S",A1)+SEARCH("N",A1)),"Not found","Found")

Whilst you can use a wildcard in SEARCH, the wildcard is there to replace a single character (?) or multiple characters (*) e.g.

If in A1 you have SEWN then =SEARCH("S*W",A1) will work
If in A1 you have SEEEEWN then =SEARCH("S*W",A1) will NOT work but =SEARCH("S?W",A1) WILL work

In either case, the characters must be in the correct order. To use search to find characters in any order you must physically check for both characters

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[wz]

Thank you! Now can I take it a step further?...

I actually want to search text in cell a1 and compare it to cell a2...

example:
a1: sw
a2: sewm

=if(iserror(search(a1,a2)),"good","alert"

if any part, in any sequence is in a1 and in a2 I want a "good" return, if not alert.

 

[wz]

P.S. I am experimenting with...

if(iserror(search(indirect(a1),a2)),"good","alert")

I just don't know where to place wildcard.

 

[wz]

Nevermind my indirect code .... (its getting late

 

[xlbo]

Will there always be 2 letters in A1?

If there are a variable number of letters then I think a VBA function will be required. for a consistent 2 letter approach, no wildcard is needed:

=IF(ISERROR(SEARCH(left(A1,1),A2)+SEARCH(right(A1,1),A2)),"Alert","good")

If you could have a variable number of letters in A1 then something like this would be needed:

Function TestMatch(fWhat As String, fIn As String) As String
Dim chkArr() As Variant
Dim blnChkFail As Boolean
Dim i As Integer

blnChkFail = False

i = Len(fWhat)

ReDim chkArr(i)

For i = 1 To Len(fWhat)

    chkArr(i - 1) = Mid(fWhat, i, 1)

Next i

For i = LBound(chkArr) To UBound(chkArr)

    If InStr(fIn, chkArr(i)) = 0 Then blnChkFail = True

Next i

If blnChkFail Then

    TestMatch = "Alert"
    
Else

    TestMatch = "Good"
    
End If

End Function

Call it using =TestMatch(A1,A2)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[wz]

WOW - you lost me

I am going to use:

=IF(ISERROR(SEARCH(B6,A1)+SEARCH(C6,A1)+SEARCH(D6,A1)+SEARCH(E6, A1)),"ALERT","GOOD")

a1 = sew
I have a table with all my letter combinations:
either s, e, w, or m in any order (stands for allergens: soy, egg, wheat, milk)

example:
b6 = s, c6 = e, d6 = w, e6 (is blank)
....

THANKS A TON!

 

[xlbo]

no worries - if you are always checking for the 4 items then that's the best way to go - the difficulty with formulas is accounting for variable iterations which the udf does

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[byundt]

It is possible to check for your allergens without needing to repeat the check for each different letter. You do this with an array formula, which can be considerable shorter.

Using the table in B6:E6, you might use the array formula:
=IF(COUNT(SEARCH(B6:E6,A1)),"ALERT","GOOD")

To create an array formula:
1) Hold Control and Shift down
2) Hit Enter
3) Release all three keys
4) Excel will respond by adding curly braces { } surrounding the formula. If not, then select the cell, click in the formula bar and repeat steps 1 through 3

Using the text "sewm" in cell A2, you might use the array formula:
=IF(COUNT(SEARCH(MID(A2,{1,2,3,4},1),A1)),"ALERT","GOOD")

If you have an arbitrary amount of text in A2, then you might use this array formula:
=IF(COUNT(SEARCH(MID(A2,ROW(INDIRECT("1:" & LEN(A2))),1),A1)),"ALERT","GOOD")

Brad

 

[xlbo]

BOOM!!! Nice formula work! Gotta love tek-tips. The more I learn, the more I realise there is to learn!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question