Compare two strings, and find difference?

EtherBoo

New Member
Joined
Oct 26, 2006
Messages
37
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?
 

marcus76

New Member
Joined
Nov 19, 2015
Messages
22
Hi AlphaFrog,

The original WORDDIF function that you've posted here is exactly what i'm looking for, but i cant get this to work.

I have as follows:

A1 = word1 word2 word3
B1 = word2

C1 = WORDDIF(A1,B1)

I get #VALUE!

...expecting word1 word3

I've followed the instructions as follows:

Alt+F11 to open the VBA Editor
From the VBA menu, select Insert\ Module
Paste the code below in the VBA Edit window

I'm running Windows 7 / Excel 2010

Any ideas greatly appreciated.


Best Rgds
Marcus
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,276
Hi Marcus and welcome to the forum.

It looks like you did it correctly. I don't know why you're getting a #VALUE! error. I tested it again by copying the original function from post #2, and used your example data and formula; it worked for me. Cell C1 returned word1 word3.

Is this your actual test data...
A1 = word1 word2 word3
B1 = word2

...or did you paraphrase to help simplify it?

About the only way I can think of producing a #VALUE! error is to type in the formula incorrectly. The formula you gave above is correct. Maybe double-check your formula on the worksheet.
 

marcus76

New Member
Joined
Nov 19, 2015
Messages
22
it is my actual test data.

Can't believe i missed this, even though i wrote it above, i made a typo..

A1 = word1 word2 word3
B1 = word2

i had :

C1 = WORDDIF(A1:B1)

works now i've changed it to:

C1 = WORDDIF(A1,B1)

i need to check what i've written in the future. thanks for the reply.

:)
 

Psynomi

New Member
Joined
Jan 14, 2016
Messages
6
Hello,
I have been using the Sub CHARDIFS to great happiness. Thank you AlphaFrog for your help. I'm using them to compare DNA sequences and it works like a charm. Now however, I'm comparing such long strings (about 150 characters) that it's hard to see the differences. Would it be possible to change the Sub to output:
A1: AGCTAG
B1: AGTTAG
New B1: ..T...

Thank you very much in advance!


A function like this cannot change formatting.

It could be done with a macro. This prompts the user to select two cells or two ranges that are the same size.

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

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,276

ADVERTISEMENT

Hello,
I have been using the Sub CHARDIFS to great happiness. Thank you AlphaFrog for your help. I'm using them to compare DNA sequences and it works like a charm. Now however, I'm comparing such long strings (about 150 characters) that it's hard to see the differences. Would it be possible to change the Sub to output:
A1: AGCTAG
B1: AGTTAG
New B1: ..T...

Thank you very much in advance!

Hi Psynomi. You're welcome and thanks for the feedback.

Try this...
Code:
[COLOR=darkblue]Sub[/COLOR] CHARDIFS()
    
    [COLOR=darkblue]Dim[/COLOR] rngA [COLOR=darkblue]As[/COLOR] Range, rngB [COLOR=darkblue]As[/COLOR] Range
    [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], r [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], c [COLOR=darkblue]As[/COLOR] Long
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]Set[/COLOR] rngA = Application.InputBox("Select the 1st cell(s) to compare to.", "Select Range A", Type:=8)
    [COLOR=darkblue]If[/COLOR] rngA [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]Do[/COLOR]
        [COLOR=darkblue]Set[/COLOR] rngB = Application.InputBox("Select the 2nd cell(s) to compare.", "Select Range B", Type:=8)
        [COLOR=darkblue]If[/COLOR] rngB [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]If[/COLOR] rngA.Columns.Count = rngB.Columns.Count And rngA.Rows.Count = rngB.Rows.Count [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Do[/COLOR]
        MsgBox "Range B muct have the same number of rows and columns as Range A.", vbExclamation, "Different Size Ranges"
        [COLOR=darkblue]Set[/COLOR] rngB = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Loop[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    
    [COLOR=darkblue]For[/COLOR] c = 1 [COLOR=darkblue]To[/COLOR] rngA.Columns.Count
        [COLOR=darkblue]For[/COLOR] r = 1 [COLOR=darkblue]To[/COLOR] rngA.Rows.Count
            strA = rngA(r, c).Value
            strB = rngB(r, c).Value
            [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] Len(strB)
                [COLOR=darkblue]If[/COLOR] i > Len(strA) [COLOR=darkblue]Or[/COLOR] UCase(Mid(strA, i, 1)) <> UCase(Mid(strB, i, 1)) [COLOR=darkblue]Then[/COLOR]
                    [COLOR=green]'rngB(r, c).Characters(Start:=i, Length:=1).Font.ColorIndex = 3[/COLOR]
                [COLOR=darkblue]Else[/COLOR]
                    rngB(r, c).Characters(Start:=i, Length:=1).Text = "."
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i, r, c
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 

Psynomi

New Member
Joined
Jan 14, 2016
Messages
6
Works like a charm! Thanks a million!

Hi Psynomi. You're welcome and thanks for the feedback.

Try this...
Code:
[COLOR=darkblue]Sub[/COLOR] CHARDIFS()
    
    [COLOR=darkblue]Dim[/COLOR] rngA [COLOR=darkblue]As[/COLOR] Range, rngB [COLOR=darkblue]As[/COLOR] Range
    [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], r [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], c [COLOR=darkblue]As[/COLOR] Long
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]Set[/COLOR] rngA = Application.InputBox("Select the 1st cell(s) to compare to.", "Select Range A", Type:=8)
    [COLOR=darkblue]If[/COLOR] rngA [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]Do[/COLOR]
        [COLOR=darkblue]Set[/COLOR] rngB = Application.InputBox("Select the 2nd cell(s) to compare.", "Select Range B", Type:=8)
        [COLOR=darkblue]If[/COLOR] rngB [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]If[/COLOR] rngA.Columns.Count = rngB.Columns.Count And rngA.Rows.Count = rngB.Rows.Count [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Do[/COLOR]
        MsgBox "Range B muct have the same number of rows and columns as Range A.", vbExclamation, "Different Size Ranges"
        [COLOR=darkblue]Set[/COLOR] rngB = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Loop[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    
    [COLOR=darkblue]For[/COLOR] c = 1 [COLOR=darkblue]To[/COLOR] rngA.Columns.Count
        [COLOR=darkblue]For[/COLOR] r = 1 [COLOR=darkblue]To[/COLOR] rngA.Rows.Count
            strA = rngA(r, c).Value
            strB = rngB(r, c).Value
            [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] Len(strB)
                [COLOR=darkblue]If[/COLOR] i > Len(strA) [COLOR=darkblue]Or[/COLOR] UCase(Mid(strA, i, 1)) <> UCase(Mid(strB, i, 1)) [COLOR=darkblue]Then[/COLOR]
                    [COLOR=green]'rngB(r, c).Characters(Start:=i, Length:=1).Font.ColorIndex = 3[/COLOR]
                [COLOR=darkblue]Else[/COLOR]
                    rngB(r, c).Characters(Start:=i, Length:=1).Text = "."
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i, r, c
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 

oneglory

New Member
Joined
Feb 10, 2016
Messages
3

ADVERTISEMENT

The WORDDIF function compares words and not individual characters. It returned ABC in your example because it evaluates ABC as a word that doesn't exist in the sentence ABCDEF.

This CHARDIF function does a case insensitive 1-to-1 character comparison.

Code:
[COLOR=darkblue]Function[/COLOR] CHARDIF(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]If[/COLOR] Len(strA) > Len(strB) [COLOR=darkblue]Then[/COLOR]
        strTemp = strA
        strA = strB
        strB = strTemp
        strTemp = ""
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        
    [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] Len(strB)
        [COLOR=darkblue]If[/COLOR] i > Len(strA) [COLOR=darkblue]Then[/COLOR]
            strTemp = strTemp & Mid(strB, i, 1)
        [COLOR=darkblue]ElseIf[/COLOR] UCase(Mid(strA, i, 1)) <> UCase(Mid(strB, i, 1)) [COLOR=darkblue]Then[/COLOR]
            strTemp = strTemp & Mid(strB, i, 1)
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i
    
    CHARDIF = strTemp
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]


This function is great. We work with a lot of URLs in my business and it's auto comparing differences at scale. This works pretty well for spotting differences at the end of a URL do you think it could spot differences in the middle of a URL?
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,276
This function is great. We work with a lot of URLs in my business and it's auto comparing differences at scale. This works pretty well for spotting differences at the end of a URL do you think it could spot differences in the middle of a URL?

Hi and welcome to the forum. I don't follow what you are describing. Perhaps you could elaborate with examples.
 

oneglory

New Member
Joined
Feb 10, 2016
Messages
3
Hi and welcome to the forum. I don't follow what you are describing. Perhaps you could elaborate with examples.

Hi,

I just reread what I wrote and realized it made absolutely no sense. Sorry about that!

I'll try to make this as least complicated as I can:

In COL A we'd have the URL and in COL B we'd have it's canonical URL sometimes the canonical doesn't match and we need to spot the difference, mostly manually which is a pain. Your current function is working great but it's only seemingly working if the difference is at the end as such:

COL A (address):
https://www.domain.com/dir1/dir2/dir3/dir4/dir5/dir6/about-us?pageid=P02057

COL B (canonical):
https://www.domain.com/dir1/dir2/dir3/dir4/dir5/dir6/about-us

DIFF (COL C, function output)
?pageid=P02057


The hangup happens when something is different in the middle:

COL A (address):
https://www.domain.com/dir1/dir2/dir3/dir4/dir5/dir6/about-us?pageid=P02057

COL B (canonical):
https://www.domain.com/folder1/dir2/dir3/dir4/dir5/dir6/about-us

DIFF (COL C, function output)
#N/A


Again, thanks for the original function - its pretty great as is.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,276
This compares two URLs folder-to-folder (items separated by forward slashes). This is similar to the WORDDIF function above.

I only tested it with the example you gave. Let me know how well it works.

Code:
[COLOR=darkblue]Function[/COLOR] URLDIF(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] ndxA [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], bJoin [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR], strTemp As [COLOR=darkblue]String[/COLOR]
        
    WordsA = Split(rngA.Text, "/")
    WordsB = Split(rngB.Text, "/")
        
    [COLOR=darkblue]For[/COLOR] ndxA = [COLOR=darkblue]LBound[/COLOR](WordsA) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](WordsA)
        [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]UBound[/COLOR](WordsB) < ndxA [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR]
        [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]StrComp[/COLOR](WordsA(ndxA), WordsB(ndxA), vbTextCompare) = 0 [COLOR=darkblue]Then[/COLOR]
            WordsA(ndxA) = vbNullString
        [COLOR=darkblue]Else[/COLOR]
            bJoin = [COLOR=darkblue]True[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] ndxA
    
    [COLOR=darkblue]If[/COLOR] bJoin [COLOR=darkblue]Then[/COLOR] URLDIF = Join(WordsA, "/")
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]
 

Watch MrExcel Video

Forum statistics

Threads
1,108,939
Messages
5,525,739
Members
409,661
Latest member
pprabha

This Week's Hot Topics

Top