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?
 
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
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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.

:)
 
Upvote 0
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]
 
Upvote 0
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]
 
Upvote 0
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]
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top