Compare strings in excel vba

Question

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

Does the order matter? In other words, does ABCDEF match CB or only BC? It was not completely apparent from your question. Maybe give some examples of things that do and don't match (by editing your question). Cheers. — Floris
– Floris, Commented Mar 12, 2013 at 15:29
you should ask this question on the Regular Expressions forum - then we in the Excel-vba forum will help you include the answer in a VBA function — Our Man in Bananas
– Our Man in Bananas, Commented Mar 12, 2013 at 16:43
I checked, and RegEx may not be the way to go, it looks like you maye have to code a function to check each letter individually and return either Match or No Match for each of the strings — Our Man in Bananas
– Our Man in Bananas, Commented Mar 12, 2013 at 17:13
@hardikudeshi - please choose an answer or ask for clarification — Our Man in Bananas
– Our Man in Bananas, Commented Mar 13, 2013 at 10:02

Community · Accepted Answer · 2020-06-20 09:12:55Z

5

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!

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 12, 2013 at 17:21

Floris

46.6k7 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Our Man in Bananas Over a year ago

I put mine into action whilst you were writing yours !

cardmagik · Accepted Answer · 2013-03-12 15:28:57Z

1

INSTR will find a substring in a string:

Typical_String = "ABZYC"

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

answered Mar 12, 2013 at 15:28

cardmagik

1,69819 silver badges18 bronze badges

2 Comments

hardikudeshi Over a year ago

This won't help for strings like ACBD,ADB

Our Man in Bananas Over a year ago

Have you thought of using a Regular Expression or RegEx Tutorial for Excel VBA

Our Man in Bananas · Accepted Answer · 2013-03-12 22:05:00Z

1

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

edited Mar 12, 2013 at 22:05

answered Mar 12, 2013 at 17:41

Our Man in Bananas

6,00525 gold badges104 silver badges158 bronze badges

5 Comments

Floris Over a year ago

You can make it more efficient by breaking out of the For loop as soon as a character doesn't match... Also - it seems like your logic currently would allow a character in the middle not to match, as long as the last one is a match?

Our Man in Bananas Over a year ago

mmm, I think the formula solution from the Mr Excel website is the best answer Floris :)

Floris Over a year ago

I completely agree it's a very clever solution; I disagree that it is the best answer: I wouldn't like to type that all over my spreadsheet. You have to enter the reference to the two cells at four different places in a very lengthy formula. Also, OP asked for a VBA function. With my solution, you only enter it once. Plus, there's quite a performance penalty to have the same equation entered multiple times (and thus being interpreted each time it's used). As for your own solution, it matches ABCD with CGA, for example...

Our Man in Bananas Over a year ago

now now Floris, you know you wouldn't type it more than once then copy down for 5 rows ... FWIW I do prefer your VBA solution to mine as mine seems to have a bug. However, to my mind a solution that avoids VBA and makes use of Excel built-in functionality will always trump a macro, no matter how complex the formula or how succinct the vba code

Floris Over a year ago

You still have to put the reference in FOUR different places in the formula - that's before copying it...: =IF((ISNA(SUM(MATCH(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0)))+ISNA(SUM(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1),0))))>1,"No Match","Match") . Whether a formula is "always better" is a matter of taste. I like VBA better... And your bug is that you don't Exit For when you find a non-match.

Alec · Accepted Answer · 2014-10-03 15:15:58Z

0

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

edited Oct 3, 2014 at 15:15

answered Oct 3, 2014 at 14:43

Alec

12 bronze badges

Collectives™ on Stack Overflow

Compare strings in excel vba

4 Answers 4

1 Comment

2 Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related