Compare two strings, and find difference?

[EtherBoo]

This may not be possible with excel, but this is what I'm trying to do

A1 = Hello John, my name is Steve, how was your day?
B1 = Hello John, my name is Steve, was day?

I need C1 to return:
how your

Is this possible?

 

[AlphaFrog]

I don't know if that helps, but I am looking for a way to compare two strings and see that their differences is either a space " " or a comma ",".

For example, let say that I have one case as "Peter Smith" and the second as "peter Smiith". Here, we have a difference of an extra space and an "i".
However, I need to filter cases when both strings have the same characters except the space. For example "Peter Smith" (e.g. so, here we have a difference of an extra space only).

I still don't understand why this wouldn't work.
This would count just the space differences between A1 and B1 and ignore other character differences.

=(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))) - (LEN(B1)-LEN(SUBSTITUTE(B1," ","")))

 

[jpaokx]

Hi,

The reason why it wouldn't work is because I don't want to ignore other characters. I need to see the only difference is the space between two strings.

In the previous example, if I have "Peter Smith" and "Peter Smeth", their differences is not only the space but also "e". That's why your function wouldn't work for my case. It considers the length of characters and not the characters themselves.
Nevertheless, I'd like to see if there is any way just to modify the previous VBA code so that it can give me which characters differ and, so, help me clean some data by filtering the characters that they differ. So, basically, not only space or comma (although this is the main part of checking) but any character that could be sensible for fixing.
I have around 50k entries to clean and I'd like to see the characters that they differ.

 

[AlphaFrog]

Try this. Note the function name is now CHARSDIF.
In the result, spaces are represented by a dash - character so you can see them.

[COLOR=darkblue]Function[/COLOR] CHARSDIF(rngA [COLOR=darkblue]As[/COLOR] Range, rngB [COLOR=darkblue]As[/COLOR] Range) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    [COLOR=darkblue]Dim[/COLOR] strA [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], strB [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], strTemp As [COLOR=darkblue]String[/COLOR]
    
    strA = rngA.Value
    strB = rngB.Value
    
    [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] Len(strB)
        [COLOR=darkblue]If[/COLOR] InStr(1, strA, Mid(strB, i, 1), 1) [COLOR=darkblue]Then[/COLOR]
            strA = Replace(strA, Mid(strB, i, 1), "", , 1, 1)
        [COLOR=darkblue]Else[/COLOR]
            strTemp = strTemp & Mid(strB, i, 1)
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i
    
    CHARSDIF = Replace(strA & strTemp, " ", "-")
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

 

[jpaokx]

Brilliant!!! This is what I needed! Thank you so much!

I don't know if it is too much, but for just a few cases, I get nothing.
For example, "Mr Peter Smith" and "Mr Peter Smith" are the same but the first case has two spaces before "peter" while the second case has two spaces after "peter". In this case, it seems that the formula offsets the outcomes and I get an empty cell.

 

[xzubv]

Hello AlphaFrog, i am trying to compare two strings and highlight the same words in the strings (i am translating strings with Dictionary, some words remain from original source). I try to negate your function, but to no avail. Is it possible to highlight same words in the second string? Thank you anyway!

 

[joeno]

Hi AlphaFrog/other,

The function works great! However what about for comparing strings of different lengths?

For example A1 - "123 KING STREET", A2 - "123 KNG ST"

I would like the output to be "IEET" or 4 so I can identify cells which are similar to each other.

 

[AlphaFrog]

Hi AlphaFrog/other,

The function works great! However what about for comparing strings of different lengths?

For example A1 - "123 KING STREET", A2 - "123 KNG ST"

I would like the output to be "IEET" or 4 so I can identify cells which are similar to each other.

See the CHARSDIF function in post #43
The result is IREET

 

[KyleMangan]

I don't know if its possible to do in excel, but I'm looking to do something similar to everyone else in this thread, but a little more complex. I want to compare two strings and return the differences between the two of them in 2 separate columns. In the first column, I'd like to return the added data to the string, and in the second column, I'd like to return what was deleted. If nothing was added or deleted I would like to return "n/a".

Example:
A1="Mike,Suzy,Ted,Amber"
B1="Mike,Ted,Amber,John"

I would like
C1 to return "John"
D1 to return "Suzy"

Is this possible in excel? If its not possible, could I return both Suzy and John without returning Amber?

The solution provided else where in this thread, WordDif, returns "Mike,Suzy,Ted,Amber" while the CharDifs macro which changes the color to red returns "Mike,Ted,Amber,John".

If I add spaces in between the names with a simple find-replace I get "-Suzy, -Amber" with WordDif and "Mike, Ted, Amber, John" with CharDifs macro. Neither of these return "John." As this is data which is refreshed daily from other sources (with thousands of entries and up to 50 names) editing the inputs seems impractical, though not impossible.

For reference the macro Chardifs is below

Sub CHARDIFS()

    
    Dim rngA As Range, rngB As Range
    Dim strA As String, strB As String
    Dim i As Long, r As Long, c As Long
    
    On Error Resume Next
    Set rngA = Application.InputBox("Select the 1st cell(s) to compare to.", "Select Range A", Type:=8)
    If rngA Is Nothing Then Exit Sub
    Do
        Set rngB = Application.InputBox("Select the 2nd cell(s) to compare.", "Select Range B", Type:=8)
        If rngB Is Nothing Then Exit Sub
        If rngA.Columns.Count = rngB.Columns.Count And rngA.Rows.Count = rngB.Rows.Count Then Exit Do
        MsgBox "Range B muct have the same number of rows and columns as Range A.", vbExclamation, "Different Size Ranges"
        Set rngB = Nothing
    Loop
    On Error Resume Next
    
    For c = 1 To rngA.Columns.Count
        For r = 1 To rngA.Rows.Count
            strA = rngA(r, c).Value
            strB = rngB(r, c).Value
            rngB(r, c + 1).Value = strB
            rngB(r, c + 1).Font.ColorIndex = xlAutomatic
            For i = 1 To Len(strB)
                If i > Len(strA) Or UCase(Mid(strA, i, 1)) <> UCase(Mid(strB, i, 1)) Then
                    rngB(r, c + 1).Characters(Start:=i, Length:=1).Font.ColorIndex = 3
                End If
    Next i, r, c
    
End Sub

and the WordDifs Module is below

Function WORDDIF(rngA As Range, rngB As Range) As String
    
    Dim WordsA As Variant, WordsB As Variant
    Dim ndxA As Long, ndxB As Long, strTemp As String
        
    WordsA = Split(rngA.Text, " ")
    WordsB = Split(rngB.Text, " ")
    
    For ndxB = LBound(WordsB) To UBound(WordsB)
        For ndxA = LBound(WordsA) To UBound(WordsA)
            If StrComp(WordsA(ndxA), WordsB(ndxB), vbTextCompare) = 0 Then
                WordsA(ndxA) = vbNullString
                Exit For
            End If
        Next ndxA
    Next ndxB
    
    For ndxA = LBound(WordsA) To UBound(WordsA)
        strTemp = strTemp & IIf(WordsA(ndxA) <> vbNullString, WordsA(ndxA), "-") & " "
    Next ndxA
    
    WORDDIF = Trim(strTemp)


End Function

Thanks for your time,

 

[AlphaFrog]

Hi Kyle and welcome to the forum. Well done on first searching for a solution.

Example:
A1="Mike,Suzy,Ted,Amber"
B1="Mike,Ted,Amber,John"

I would like
C1 to return "John"
D1 to return "Suzy"

Is this possible in excel? If its not possible, could I return both Suzy and John without returning Amber?

Try this WORDMISS function. It uses a comma as the word delimiter which you can change in the code to suit.

Put this formula in C1
=WORDMISS(A1,B1)

And this formula in D1
=WORDMISS(B1,A1)

[COLOR=darkblue]Function[/COLOR] WORDMISS(rngA [COLOR=darkblue]As[/COLOR] Range, rngB [COLOR=darkblue]As[/COLOR] Range) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    [COLOR=darkblue]Dim[/COLOR] WordsA [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], WordsB [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] ndxB [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], strTemp As [COLOR=darkblue]String[/COLOR]
    
    strDelimter = ","   [COLOR=green]'Change word delimiter to suit[/COLOR]
    WordsA = strDelimter & rngA.Text & strDelimter
    WordsB = Split(rngB.Text, strDelimter)
    
    [COLOR=darkblue]For[/COLOR] ndxB = [COLOR=darkblue]LBound[/COLOR](WordsB) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](WordsB)
        [COLOR=darkblue]If[/COLOR] InStr(1, WordsA, strDelimter & WordsB(ndxB) & strDelimter, vbTextCompare) = 0 [COLOR=darkblue]Then[/COLOR]
            strTemp = strTemp & strDelimter & WordsB(ndxB)
        [COLOR=darkblue]Else[/COLOR]
            WordsA = Replace(WordsA, WordsB(ndxB), vbNullString, 1, 1)
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] ndxB
    
    [COLOR=darkblue]If[/COLOR] Len(strTemp) [COLOR=darkblue]Then[/COLOR] WORDMISS = Mid(strTemp, 2)
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

 

[KyleMangan]

Awesome! Thank you so much for the unbelievably quick response!