Compare two strings 3

[Palmyra]

I'm trying to compare names. I have two strings. String1 will have one name, e.g., 'JohnSmith'. String2 may have more than one name, e.g., 'John Smith; Mary Smith; Joan Smith'.

I need to be able to tell if John Smith in String1 is anywhere in String2.

Any help appreciated.

 

[SkipVought]

Hi,

You you will never find 'JohnSmith' in 'John Smith; Mary Smith; Joan Smith'.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[rmikesmith]

Palmyra,

Skip is quite right that, as those strings are presented, you cannot find one in the other using builtin functions. However, if you really are trying to locate a name in the form of 'JohnSmith', with no space between, in a longer string with normal spacing, try this function:

Function NameFound(ByVal sStringToSearch As String, ByVal sSearchFor As String) As Boolean
Dim sTmp As String
Dim Pos As Long
    
  sTmp = Replace(sStringToSearch, Chr(32), "", 1, -1, vbTextCompare)
  Pos = InStr(1, sTmp, sSearchFor, vbTextCompare)
  NameFound = (Pos > 0)
  
End Function

Regards,
Mike

 

[firefytr]

You could parse for proper sensitivity of the words..

Sub foofoo()
    Dim string1 As String, string2 As String, i As Long, strArray() As Variant
    string1 = "JohnSmith"
    string2 = "John Smith; Mary Smith; Joan Smith"
    For i = 1 To Len(string1)
        If Mid(string1, i, 1) = UCase(Mid(string1, i, 1)) And i <> 1 Then
            strArray = Array(Left(string1, i - 1), Right(string1, Len(string1) - i + 1))
            Exit For
        End If
    Next i
    string1 = vbNullString
    For i = LBound(strArray) To UBound(strArray)
        string1 = string1 & strArray(i) & " "
    Next i
    string1 = Trim(string1)
    'check again...
    MsgBox string2 Like CStr("*" & string1 & "*")
End Sub

This will exit on the first capitalized letter found in string1.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[PHV]

What about this one-liner ?

MsgBox string1 & " in " & string2 & "=" & (";" & UCase(Replace(string2, " ", "")) & ";" Like "*;" & UCase(Replace(string1, " ", "")) & ";*")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[firefytr]

Well I'll be a backwards .... Nice.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[missinglinq]

Of course, the real question is, does the OP want to find 'JohnSmith' in String2 or 'John Smith' in String2! In his post he actually states both! And if there will always be a space between the first and last name in the search term, a simple instr() will do the job!


The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[Palmyra]

Thank you all for the great answers. However, the specs have now changed.

What I'm going to have is:

Is user 'abc100' (string1) to be found anywhere in ';abc100; def100' (string2)

I tried:

If (InStr(string1), string2) = 1) Then
MsgBox "Yes"
End If

Seems like it should return a 1 for true, but it doesn't.

Any help is appreciated.

 

[strongm]

Get your brackets correct, and instead of

=1

try

<>0

 

[missinglinq]

InStr() doesn't return True or False, it returns the position within the searched string where the searched for string begins, so anything greater than zero means the string was found!

If InStr(string1, string2)> 0 Then
        MsgBox "Yes"
End If

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[strongm]

>InStr() doesn't return True or False

Depends on your point of view ...

 

[firefytr]

You need to add the Start property as the first syntax..

If InStr(1, string1, string2, vbtextcompare) <> 0 then
    Msgbox "yes"
else
    Msgbox "no"
end if

While this is fast, and the way I would choose, you can also do this in an array. So, if your data is in an array, however you choose to do that, you can just use the Match() function, i.e. ...

Dim x as variant, strTest as string, arrTest() as string
strtest = "abc111,abc222,abc333,abc444"
arrTest = split(strtest, ",")
on error resume next 'error will be generated if not found
x = worksheetfunction.match("abc222", arrtest, 0)
if isempty(x) then
    msgbox "not found"
else
    msgbox "found in position " & x
end if

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[missinglinq]

strongm, from Help:

"Instr()
Returns a Variant (Long) specifying the position of the first occurrence of one string within another."

firefytr

Not true! Start is an optional argument! It's default value is 1, so why bother including it? You only include Start if you want to start the search from somewhere other than the first character.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[firefytr]

Yes, I realize it is optional Missinglinq. I just think it is good practice. There are many instances like this in Excel, where a syntax is not needed, but should be used anyway. Sometimes, although not in this case, it can be detrimental if you do not. Just because you don't *have* to do it, doesn't mean you shouldn't. Another example is Option Explicit. Why it is optional I have no idea, but it is.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[strongm]

I know what the Help says.

However, in VB/VBA False is defined as 0 and, contrary to popular opnion, True is actually any value that is not False (-1 is selected as a default value for True as a matter of convenience; in C++ the convenience value is 1 instead).

This means that, from one perspective, Instr is False (0) if there is no match, and True (Not False) if there is.

 

[Palmyra]

I tried (testing with data, not variable)

If InStr("ABC120", ";ABC120; DEF120;") > 0 Then
MsgBox "yes"
End If

Doesn't return "Yes"

Tried below

If InStr(1, ("ABC120", ";ABC120; DEF120;", vbtextcompare) <> 0 then
Msgbox "yes"
else
Msgbox "no"
end if

Doesn't return "Yes"

Thanks.

 

[PHV]

Please, put the cursor inside the InStr word in your code and then press the F1 key.

 

[Palmyra]

PHV, so it looks like below works, but for my purposes,where I'm looking for the first string to be anywhere in the second string, this will not work.

If InStr(1, "abc120", "abc120", vbTextCompare) = 1 Then
MsgBox "yes"
End If

Thanks.

 

[rmikesmith]

Palmyra,

PHV was trying to get you to discover on your own that you have the two strings reversed within the Instr function: String to search in comes first!


Regards,
Mike

 

[PHV]

Did you carefully read the help ?
Look at what are string1 and string2 ...