Compare two String in Excel-CodePudding

Can anyone help me with formula to compare two strings in excel using excel formulae or vba macros

Example :-

str 1 : abcd def
str 2 : def abcd

If we compare str 1 and str 2 then it should return true

Thanks in advance

str 1 : abcd def
str 2 : def abcd

=str1 = str2

CodePudding user response：

I assume that you want to compare the single words of the two string, but in any order. There is no build-in function in Excel or VBA.

The following function splits both strings into single words (using the split-function) and check for every word of the first string if it is also present in the second string. I have added a small regex-code to get rid of multiple spaces or tab characters in the two strings, if you are sure you have always only one space between the words, you can remove the lines

Function compareStrings(string1 As String, string2 As String) As Boolean
    
    ' Replace multiple spaces and tabs
    Dim regX As Object
    Set regX = CreateObject("VBScript.RegExp")
    regX.Pattern = "\s{2,}"
    regX.Global = True
    string1 = regX.Replace(string1, " ")
    string2 = regX.Replace(string2, " ")
    
    ' Split both strings into single words
    Dim string1Tokens() As String, string2Tokens() As String        
    string1Tokens = Split(string1, " ")
    string2Tokens = Split(string2, " ")
    ' If we have a different number of words we don't need to continue.

    If UBound(string1Tokens) <> UBound(string2Tokens) Then Exit Function
    Dim i1 As Long, i2 As Long
    For i1 = LBound(string1Tokens) To UBound(string1Tokens)
        Dim wordFound As Boolean
        wordFound = False
        For i2 = LBound(string2Tokens) To UBound(string1Tokens)
            If string1Tokens(i1) = string2Tokens(i2) Then
                wordFound = True
                Exit For
            End If
        Next
        ' Word of first string was not found.
        If Not wordFound Then Exit Function
    Next
    ' All words where found
    compareStrings = True
End Function

CodePudding user response：

Another approach would be to sum the character code for each string and compare their values. First check the length of both strings to avoid all this if they're not the same.

Honestly, I have no idea where this would be useful. :)

Const S1 As String = "abcd def"
Const S2 As String = "def abcd"

Debug.Print WordSum(S1) = WordSum(S2)
'True

Function WordSum(ByVal word As String) As Long

    Dim w As Variant
    
    For Each w In Split(word)
        WordSum = WordSum   Asc(w)
    Next
    
End Function

CodePudding user response：

As Kostas mentions in comments, splitting-sorting-joining the strings would allow you to compare two strings, regardless of the order of words that make up those strings:

Function name_compare(n1 As String, n2 As String) As Boolean
    n1 = Join(sort_array(Split(UCase(n1), " ")), " ")
    n2 = Join(sort_array(Split(UCase(n2), " ")), " ")
    name_compare = (n1 = n2)
End Function

Function sort_array(arr)
    Dim i As Long, j As Long, temp As String
    For i = LBound(arr) To UBound(arr) - 1
       For j = i   1 To UBound(arr)
          If arr(i) > arr(j) Then
             temp = arr(j)
             arr(j) = arr(i)
             arr(i) = temp
          End If
      Next j
    Next i
    sort_array = arr
End Function

You can use this function like so:

Const name1 As String = "abcd def"
Const name2 As String = "def abcd"
Debug.Print name_compare(name1, name2)

Result:

True