CODEWITHSUNDEEP
stringexcelvba
hardikudeshi
hardikudeshi

Reputation: 1491

Compare strings in excel vba

I have a bunch of strings comprising of characters "A","B"..."Z" (and no others). A typical string looks like ABZYC. The strings are given to me in pairs like ABC,ABDC. The strings are comparable if one string is contained in the other (i.e either one of the two strings contain all the alphabets of the other). The order in which the string appears don't matter.

Is there any direct function in excel vba which does this sort of comparison?

Examples:
ACBD,AC - Match
ACBD,CA - Match
ACBD,ADB - Match
AC,ABCD - Match
ABC, ABD - No Match

Upvotes: 1

Views: 20041

Answers (4)

Alec
Alec

Reputation: 1

I missread the post!

Use function EXACT

Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences.

I usually add the function UPPER ie:

A1 = Some Place
B1 = some place

with

=EXACT(UPPER(A1),UPPER(B1)) = EXACT(SOME PLACE, SOME PLACE) = TRUE

Without UPPER

=EXACT(A1,B1) = EXACT(Some Place, some place) = FALSE

Upvotes: 0

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

If you want a Formula solution, a user called Schielrn on the Mr Excel forum site came up with this sublime masterpiece (using ARRAY FORMULAS)

Or, if you want a VBA, try this...

Sub compare()

Dim iIndx As Integer
Dim str1 As String
Dim str2 As String
Dim sLetter As String
Dim bFound As Boolean

Range("A1").Select
bFound = False

Do

    str1 = VBA.Trim(ActiveCell.Text)
    str2 = VBA.Trim(ActiveCell.Offset(0, 1).Text)

    For iIndx = 1 To Len(str1)
        If VBA.InStr(str2, VBA.Mid(str1, iIndx, 1)) <> "" Then
            ' found it
            bFound = True
        Else
            bFound = False
            exit for
        End If
    Next

    If bFound = False Then
    ' check the other way!
        For iIndx = 1 To Len(str2)
            If VBA.InStr(str1, VBA.Mid(str2, iIndx, 1)) <> "" Then
                ' found it
                bFound = True
            Else
                bFound = False
            exit for
            End If
        Next
    End If

    If bFound = True Then ActiveCell.Offset(0, 2).Value = "MATCHED!"

    ActiveCell.Offset(1, 0).Select
Loop While Not ActiveCell.Offset(1, 0).Text = ""

End Sub

Upvotes: 1

Floris
Floris

Reputation: 46365

Add the following function in a module in your workbook:

Function allIn(str1, str2)
' check whether all elements of str1 occur in str2
' and vice versa
Dim l1, l2, ii As Integer
Dim isfound As Boolean

isfound = True

l1 = Len(str1)
l2 = Len(str2)

If l1 < l2 Then
' look for all the elements of str1 in str2
  For ii = 1 To l1
    If InStr(1, str2, Mid(str1, ii, 1), vbTextCompare) <= 0 Then
      isfound = False
      Exit For
    End If
  Next ii
Else
' look for all the elements of str2 in str1
  For ii = 1 To l2
    If InStr(1, str1, Mid(str2, ii, 1), vbTextCompare) <= 0 Then
      isfound = False
      Exit For
    End If
  Next ii
End If
allIn = isfound
End Function

Now you can call this from another place in your code, using result = inStr("ABD", "BAD") - or from the spreadsheet itself. On the spreadsheet you would type =allIn(A3, B6) to compare strings in cells A3 and B6.

Here is what happens when I did that (I entered =allIn(A1, B1) in cell C1, then dragged the formula to the next four rows):

screen shot of spreadsheet

I believe that solves your problem.

EDIT: I just noticed @Philip's comment to your question - I appear to have implemented his suggestion although I had not seen it when I started to compose it... But here's a tip of the hat all the same!

Upvotes: 5

cardmagik
cardmagik

Reputation: 1698

INSTR will find a substring in a string:

Typical_String = "ABZYC"

if instr(Typical_String,"ABC") > 0 then

Upvotes: 1

Related Questions