Compare two strings, and find difference?

E

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?
 
Sort by date Sort by votes
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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!


AlphaFrog said:
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]
Click to expand...
 
Upvote 0
Psynomi said:
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!
Click to expand...

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!

AlphaFrog said:
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]
Click to expand...
 
Upvote 0
AlphaFrog said:
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]
Click to expand...


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
oneglory said:
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?
Click to expand...

Hi and welcome to the forum. I don't follow what you are describing. Perhaps you could elaborate with examples.
 
Upvote 0
AlphaFrog said:
Hi and welcome to the forum. I don't follow what you are describing. Perhaps you could elaborate with examples.
Click to expand...

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
You must log in or register to reply here.

Similar threads

B
  • Solved
OPTIMIZE my code
Replies
5
Views
303
BritsBlitz
B
E
  • Solved
Help with "Kill" command
Replies
2
Views
168
EssKayKay
E
E
  • Solved
VBA to create range name based on value of cells in the range?
Replies
3
Views
247
SeniorNewbie
SeniorNewbie
S
  • Solved
When Filter is applied, Excel jumps down multiple rows to (Blanks). How can Filter ignore blanks?
Replies
3
Views
300
Peter_SSs
Peter_SSs
A
  • Question Question
Automate Attendance Marking Based on Daily Schedule in Excel
Replies
1
Views
197
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,920
Messages
6,175,373
Members
452,638
Latest member
Oluwabukunmi

Share this page

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