Location of string differences

[Chris Watts]

I have a problem with comparing two strings, string1 and string2. string2
will be a manually edited version of string1 so will have had
words/punctuation added/deleted and characters changed to/from Caps.

Comparing the two strings (in vba, Excel 2007) to find if they are identical
(case sensitive) is not a problem as I can use strcompar(). but I wish to
locate where the differences occur. Detecting the first one would help but
it would be nice to continue and find where they now become identical again.

Any suggestion would be appreciated. I am using Excel 2007 under Windows XP
sp2 and have quite good vba skills.

TIA
Chris

 

[Don Guillett]

I have a problem with comparing two strings, string1 and string2.  string2
will be a manually edited version of string1 so will have had
words/punctuation added/deleted and characters changed to/from Caps.

Comparing the two strings (in vba, Excel 2007) to find if they are identical
(case sensitive) is not a problem as I can use strcompar(). but I wish to
locate where the differences occur.  Detecting the first one would helpbut
it would be nice to continue and find where they now become identical again.

Any suggestion would be appreciated.  I am using Excel 2007 under Windows XP
sp2 and have quite good vba skills.

TIA
Chris

http://www.google.com/#hl=en&cp=20&...r_gc.r_pw.&fp=80a7ff9ca756264&biw=888&bih=559

Could be easy or difficult depending on your text. Perhaps data>text
to columns to break up and then compare or something like this. If all
else fails you could do a for i= 1 to len(a1) to compare character by
character....

========http://dmcritchie.mvps.org/excel/strings.htm
String Comparisons, compare to second space (based on posting
2003-01-090

Formulas include concatenation of two spaces to guarantee finding the
second space in each case.
a14: BA 12345 10-30-2003 must be ---> BA 12345
B14: NM 12345 AB 40893 Must be ----> NM 12345
D14" =IF(UPPER(LEFT(A14 & " ",FIND(" ",RIGHT(A14 & " ",LEN(A14 & "
")
-FIND(" ",A14 & " ")))+FIND(" ",A14 & " ")))=
UPPER(LEFT(B14 & " ",FIND(" ",RIGHT(B14 & " ",LEN(B14 & "
")
-FIND(" ",B14 & " ")))+FIND(" ",B14 & "
"))),"Equal","Differs")

or using a programmed User Defined Function

A14: BA 12345 x
B14: BA 1234
C14: ba 12345
D14: =CompareTruncate(A14,B14) -- shows Differs
E14: =CompareTruncate(A14,C14) -- Equal

Function CompareTruncate(ByVal string1 As String, ByVal _
String2 As String)
string1 = Left(string1 & " ", InStr(InStr(string1 & " ", " ") +
1, _
string1 & " ", " "))
String2 = Left(String2 & " ", InStr(InStr(String2 & " ", " ") +
1, _
String2 & " ", " "))
'-- CompareTruncate = UCase(string1) = UCase(String2)--replaced
by...
CompareTruncate = InStr(1, String2, string1, vbTextCompare) = 1
End Function

 

[Ron Rosenfeld]

I have a problem with comparing two strings, string1 and string2. string2
will be a manually edited version of string1 so will have had
words/punctuation added/deleted and characters changed to/from Caps.

Comparing the two strings (in vba, Excel 2007) to find if they are identical
(case sensitive) is not a problem as I can use strcompar(). but I wish to
locate where the differences occur. Detecting the first one would help but
it would be nice to continue and find where they now become identical again.

Any suggestion would be appreciated. I am using Excel 2007 under Windows XP
sp2 and have quite good vba skills.

TIA
Chris

Chris,

Please provide some examples of your inputs and desired outputs.

One thought that comes to mind is that you could do a character by character comparison, building up a binary value, and work from that.

 

[Chris Watts]

I have a problem with comparing two strings, string1 and string2. string2
will be a manually edited version of string1 so will have had
words/punctuation added/deleted and characters changed to/from Caps.

Comparing the two strings (in vba, Excel 2007) to find if they are
identical
(case sensitive) is not a problem as I can use strcompar(). but I wish to
locate where the differences occur. Detecting the first one would help but
it would be nice to continue and find where they now become identical
again.

Any suggestion would be appreciated. I am using Excel 2007 under Windows
XP
sp2 and have quite good vba skills.

TIA
Chris

http://www.google.com/#hl=en&cp=20&...r_gc.r_pw.&fp=80a7ff9ca756264&biw=888&bih=559

Could be easy or difficult depending on your text. Perhaps data>text
to columns to break up and then compare or something like this. If all
else fails you could do a for i= 1 to len(a1) to compare character by
character....

========http://dmcritchie.mvps.org/excel/strings.htm
String Comparisons, compare to second space (based on posting
2003-01-090

Formulas include concatenation of two spaces to guarantee finding the
second space in each case.
a14: BA 12345 10-30-2003 must be ---> BA 12345
B14: NM 12345 AB 40893 Must be ----> NM 12345
D14" =IF(UPPER(LEFT(A14 & " ",FIND(" ",RIGHT(A14 & " ",LEN(A14 & "
")
-FIND(" ",A14 & " ")))+FIND(" ",A14 & " ")))=
UPPER(LEFT(B14 & " ",FIND(" ",RIGHT(B14 & " ",LEN(B14 & "
")
-FIND(" ",B14 & " ")))+FIND(" ",B14 & "
"))),"Equal","Differs")

or using a programmed User Defined Function

A14: BA 12345 x
B14: BA 1234
C14: ba 12345
D14: =CompareTruncate(A14,B14) -- shows Differs
E14: =CompareTruncate(A14,C14) -- Equal

Function CompareTruncate(ByVal string1 As String, ByVal _
String2 As String)
string1 = Left(string1 & " ", InStr(InStr(string1 & " ", " ") +
1, _
string1 & " ", " "))
String2 = Left(String2 & " ", InStr(InStr(String2 & " ", " ") +
1, _
String2 & " ", " "))
'-- CompareTruncate = UCase(string1) = UCase(String2)--replaced
by...
CompareTruncate = InStr(1, String2, string1, vbTextCompare) = 1
End Function

Thanks Don, I'll need to study that. The vba avenue is the one that I wish
to follow as it will be part of a very much more substantial piece of code.

Chris

 

[Chris Watts]

Chris,

Please provide some examples of your inputs and desired outputs.

One thought that comes to mind is that you could do a character by
character comparison, building up a binary value, and work from that.

Sort of thing that I have in mind is:

String1 = "Now is the time for all good men to to come to the aid of the
part.."
String2 = "NOW is the time for all good persons to come to the aid of the
local party."

I wish to flag up in String1 that NOW and part/y are changes; persons as a
substitution, local as an addition and to as a deletion.

String1 may also be interspersed with tags eg <bold> and </bold> to indicate
formatting but not in String2 and need to be ignored.


I guess this is a bit like the Word change tracking process but I need it in
Excel. I guess I could instantiate a Word object and operate on that but
previous attempts with usingthat technique have worked but left a trail of
unclosed Word files - even though I specifically killed the object - and it
was very very slow. The latter point is significant as I may have several
hundred strings to process.


cheers
Chris

 

[Don Guillett]

http://www.google.com/#hl=en&cp=20&gs_id=28&xhr=t&q=excel:compare+str...

Could be easy or difficult depending on your text. Perhaps data>text
to columns to break up and then compare or something like this. If all
else fails you could do a for i= 1 to len(a1) to compare character by
character....

========http://dmcritchie.mvps.org/excel/strings.htm
String Comparisons, compare to second space   (based on posting
2003-01-090

Formulas include concatenation of two spaces to guarantee finding the
second space in each case.
a14:   BA 12345 10-30-2003 must be ---> BA 12345
B14:   NM 12345 AB 40893 Must be ----> NM 12345
D14"  =IF(UPPER(LEFT(A14 & "  ",FIND(" ",RIGHT(A14 & "  ",LEN(A14& "
")
            -FIND(" ",A14 & "  ")))+FIND(" ",A14 & "  ")))=
          UPPER(LEFT(B14 & "  ",FIND(" ",RIGHT(B14 & "  ",LEN(B14 & "
")
            -FIND(" ",B14 & "  ")))+FIND(" ",B14 & "
"))),"Equal","Differs")

or  using a programmed User Defined Function

A14:  BA 12345 x
B14:  BA 1234
C14:  ba 12345
D14: =CompareTruncate(A14,B14)  -- shows Differs
E14: =CompareTruncate(A14,C14)  -- Equal

Function CompareTruncate(ByVal string1 As String, ByVal _
      String2 As String)
    string1 = Left(string1 & "  ", InStr(InStr(string1 & "  ", " ") +
1, _
        string1 & "  ", " "))
    String2 = Left(String2 & "  ", InStr(InStr(String2 & "  ", " ") +
1, _
        String2 & "  ", " "))
    '-- CompareTruncate = UCase(string1) = UCase(String2)--replaced
by...
    CompareTruncate = InStr(1, String2, string1, vbTextCompare) =1
End Function

Thanks Don, I'll need to study that.  The vba avenue is the one that I wish
to follow as it will be part of a very much more substantial piece of code.

Chris- Hide quoted text -

- Show quoted text -

I would think that the best way to do this would be to identify the
text if NOT the same and then MANUALLY correct.

 

[Chris Watts]

http://www.google.com/#hl=en&cp=20&gs_id=28&xhr=t&q=excel:compare+str...

Could be easy or difficult depending on your text. Perhaps data>text
to columns to break up and then compare or something like this. If all
else fails you could do a for i= 1 to len(a1) to compare character by
character....

========http://dmcritchie.mvps.org/excel/strings.htm
String Comparisons, compare to second space (based on posting
2003-01-090

Formulas include concatenation of two spaces to guarantee finding the
second space in each case.
a14: BA 12345 10-30-2003 must be ---> BA 12345
B14: NM 12345 AB 40893 Must be ----> NM 12345
D14" =IF(UPPER(LEFT(A14 & " ",FIND(" ",RIGHT(A14 & " ",LEN(A14 & "
")
-FIND(" ",A14 & " ")))+FIND(" ",A14 & " ")))=
UPPER(LEFT(B14 & " ",FIND(" ",RIGHT(B14 & " ",LEN(B14 & "
")
-FIND(" ",B14 & " ")))+FIND(" ",B14 & "
"))),"Equal","Differs")

or using a programmed User Defined Function

A14: BA 12345 x
B14: BA 1234
C14: ba 12345
D14: =CompareTruncate(A14,B14) -- shows Differs
E14: =CompareTruncate(A14,C14) -- Equal

Function CompareTruncate(ByVal string1 As String, ByVal _
String2 As String)
string1 = Left(string1 & " ", InStr(InStr(string1 & " ", " ") +
1, _
string1 & " ", " "))
String2 = Left(String2 & " ", InStr(InStr(String2 & " ", " ") +
1, _
String2 & " ", " "))
'-- CompareTruncate = UCase(string1) = UCase(String2)--replaced
by...
CompareTruncate = InStr(1, String2, string1, vbTextCompare) = 1
End Function

Thanks Don, I'll need to study that. The vba avenue is the one that I wish
to follow as it will be part of a very much more substantial piece of
code.

Chris- Hide quoted text -

- Show quoted text -

I would think that the best way to do this would be to identify the
text if NOT the same and then MANUALLY correct.

I am not looking to make manual corrections but to flag the position of the
difference for manual correction
When you have several thousand cases (strings) where there are differences
it would be highly beneficial to try and flag the site of the difference
when the text string is likely to be several hundred characters long!

 

[Salmon Egg]

I have a problem with comparing two strings, string1 and string2. string2
will be a manually edited version of string1 so will have had
words/punctuation added/deleted and characters changed to/from Caps.

Comparing the two strings (in vba, Excel 2007) to find if they are identical
(case sensitive) is not a problem as I can use strcompar(). but I wish to
locate where the differences occur. Detecting the first one would help but
it would be nice to continue and find where they now become identical again.

Any suggestion would be appreciated. I am using Excel 2007 under Windows XP
sp2 and have quite good vba skills.

TIA
Chris

I was hoping that responders would provide a simple solution. That was
wishful thinking.

In your formulation of the problem you wrote, "...it would be nice to
continue and find where they now become identical again." The problem
lies in the definition of "become identical again." For example, where
does "I want to interpret your body language" become identical again
with "I want to read your book report"?

--

Sam

Conservatives are against Darwinism but for natural selection.
Liberals are for Darwinism but totally against any selection.