信件信比较 [英] Letter by letter Comparison

问题描述

我有两组数据在两个单元格（A1和B1）中没有任何特殊字符（。，/ ;:' - @＃$％^& *（）{} []）单词，

问题是我需要比较两个单元格，并识别并突出显示差异。

例如：

 （A1）：howtobuilfmacroincludingthesecrria 
（B1）：howbuilfmacroincludingthesecriteria 
  

• 在A1 ite 缺少


• 和B1 到缺少

宏应该突出显示A1中的 ite 和A1中的至

解决方案

确保文本字符串在单元格A1和B1中。

将这些例程放在标准代码模块（Alt-F11）中。 / p>

运行FindDistinctSubstrings例程（工作表中的Alt-F8）。

  Public Sub FindDistinctSubstrings（）
 Dim a $，b $ 
a = [a1] 
b = [b1] 
 S1inS2 0,2，a，b，[a1]，vbRed 
 S1inS2 0,2，b，a，[b1]，vbRed 
 S1inS2 1,3 ，a，b，[a1]，vbBlack 
 S1inS2 1，3，b，a，[b1]，vbBlack 
 End Sub 
 Private Sub S1inS2（yes& k& s1 $ ，s2 $，r As Range，color&）
 Dim i& 
对于i = 1 To Len（s1）
 If（yes = 0 And 0 = InStr（s2，Mid $（s1，i，k）））或（是= 1而0  r.Characters（i，k）.Font.color = color 
 End If 
 Next 
 End Sub 
  

I have 2 sets of data in two cells (A1 and B1) without any special character (.,/;:'"-@#$%^&*(){}[]) and also no space between words,

The problem is I need to compare both the cells and identify and highlight the difference.

For example :

(A1): howtobuilfmacroincludingthesecrria
(B1): howbuilfmacroincludingthesecriteria

• in A1 ite is missing
• and B1 to is missing

The macro should highlight ite in B1 and to in A1

解决方案

Make sure the text strings are in cells A1 and B1.

Place these routines in a standard code module (Alt-F11).

Run the FindDistinctSubstrings routine (Alt-F8 from the worksheet).

Public Sub FindDistinctSubstrings()
    Dim a$, b$
    a = [a1]
    b = [b1]
    S1inS2 0, 2, a, b, [a1], vbRed
    S1inS2 0, 2, b, a, [b1], vbRed
    S1inS2 1, 3, a, b, [a1], vbBlack
    S1inS2 1, 3, b, a, [b1], vbBlack
End Sub
Private Sub S1inS2(yes&, k&, s1$, s2$, r As Range, color&)
    Dim i&
    For i = 1 To Len(s1)
        If (yes = 0 And 0 = InStr(s2, Mid$(s1, i, k))) Or (yes = 1 And 0 < InStr(s2, Mid$(s1, i, k))) Then
            r.Characters(i, k).Font.color = color
        End If
    Next
End Sub

这篇关于信件信比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！