如何在Excel中提取两段文本的差异值并单独显示在单元格中

一、公式实现（字符级差异提取） #

如果你的Excel版本支持TEXTJOIN和SEQUENCE（如365/2021），可直接用以下公式提取两段文本的差异字符：

=TEXTJOIN("",TRUE,IF(MID(H11,SEQUENCE(LEN(H11)),1)<>MID(I11,SEQUENCE(LEN(H11)),1),MID(H11,SEQUENCE(LEN(H11)),1)&"["&MID(I11,SEQUENCE(LEN(H11)),1)&"]",""))

若为旧版Excel，使用数组公式（输入后按Ctrl+Shift+Enter确认）：

=TEXTJOIN("",TRUE,IF(MID(H11,ROW(INDIRECT("1:"&MAX(LEN(H11),LEN(I11)))),1)<>MID(I11,ROW(INDIRECT("1:"&MAX(LEN(H11),LEN(I11)))),1),MID(H11,ROW(INDIRECT("1:"&MAX(LEN(H11),LEN(I11)))),1)&"["&MID(I11,ROW(INDIRECT("1:"&MAX(LEN(H11),LEN(I11)))),1)&"]",""))

该公式会逐个对比字符，将差异部分以[原字符→新字符]格式合并展示，直观呈现不同点。

二、VBA自定义函数（支持字符/单词级差异） #

1. 按下Alt+F11打开VBA编辑器，插入新模块；
2. 粘贴以下代码：

Function GetTextDiff(text1 As String, text2 As String, Optional compareByWord As Boolean = False) As String
    Dim arr1, arr2
    Dim i As Integer, maxLen As Integer
    Dim diff As String
    
    If compareByWord Then
        '按空格分隔的单词对比
        arr1 = Split(text1, " ")
        arr2 = Split(text2, " ")
        maxLen = IIf(UBound(arr1) > UBound(arr2), UBound(arr1), UBound(arr2))
        
        For i = 0 To maxLen
            Dim val1 As String, val2 As String
            val1 = IIf(i <= UBound(arr1), arr1(i), "")
            val2 = IIf(i <= UBound(arr2), arr2(i), "")
            
            If val1 <> val2 Then
                diff = diff & "[" & val1 & "→" & val2 & "] "
            End If
        Next i
    Else
        '按字符对比
        maxLen = IIf(Len(text1) > Len(text2), Len(text1), Len(text2))
        
        For i = 1 To maxLen
            Dim char1 As String, char2 As String
            char1 = IIf(i <= Len(text1), Mid(text1, i, 1), "")
            char2 = IIf(i <= Len(text2), Mid(text2, i, 1), "")
            
            If char1 <> char2 Then
                diff = diff & "[" & char1 & "→" & char2 & "]"
            End If
        Next i
    End If
    
    GetTextDiff = Trim(diff)
End Function

3. 返回Excel，直接调用函数：
   • 字符级对比：=GetTextDiff(H11,I11)
   • 单词级对比：=GetTextDiff(H11,I11,TRUE)

函数会将差异内容以[原内容→新内容]格式返回，精准提取两段文本的不同之处。

补充说明 #

EXACT仅能判断文本是否完全一致，条件格式仅能高亮差异位置，均无法直接提取差异内容。上述公式与VBA方法可解决该需求。

内容的提问来源于stack exchange，提问作者Bala