vba 如何区分 Excel 中两个字符串之间的差异？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43351835/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I tell the differences between two strings in Excel?
提问by DukeSilver
I created an assessment that applicants fill out in Excel. I have a key where I copy their answers in and it tells me if their answers match my key. I'd like to add a formula that will also show me the differences between the applicant's cell (B2) and the key's cell (A2), to make it easier to see what they got wrong.
我创建了一个评估，申请人在 Excel 中填写。我有一把钥匙，我可以将他们的答案复制进去，它会告诉我他们的答案是否与我的钥匙相符。我想添加一个公式，该公式还将向我显示申请人的单元格 (B2) 和密钥的单元格 (A2) 之间的差异，以便更容易看出他们出错的地方。
I tried using =SUBSTITUTE(B2,A2,"")but this only gives me differences at the beginning or end of the string. Usually, the difference is in the middle.
我尝试使用，=SUBSTITUTE(B2,A2,"")但这只会在字符串的开头或结尾给出差异。通常，差异在中间。
For example, my key (cell A2) might say: Cold War | Bay of Pigs | Fidel Castro
例如，我的密钥（单元格 A2）可能会说：Cold War | 猪湾 | 菲德尔·卡斯特罗
And the applicant (cell B2) might say: Cold War | Cuban Missile Crisis | Fidel Castro
申请人（单元格 B2）可能会说：冷战 | 古巴导弹危机| 菲德尔·卡斯特罗
I want this formula to return: "Cuban Missile Crisis"
我希望这个公式返回：“古巴导弹危机”
回答by Subodh Tiwari sktneer
You may try something like this...
你可以试试这样的...
Function CompareStrings(keyRng As Range, ansRng As Range) As String
Dim arr() As String
Dim i As Long
arr() = Split(ansRng.Value, "|")
For i = 0 To UBound(arr)
If InStr(keyRng.Value, arr(i)) = 0 Then
CompareStrings = arr(i)
Exit Function
End If
Next i
End Function
Then you can use this UDF like below...
然后你可以像下面一样使用这个UDF......
=CompareStrings(A2,B2)
If you want to compare them in the reverse order also and return the not matched string part from any of them, try this...
如果你还想以相反的顺序比较它们并从它们中的任何一个返回不匹配的字符串部分，试试这个......
Function CompareStrings(ByVal keyRng As Range, ByVal ansRng As Range) As String
Dim arr() As String
Dim i As Long
Dim found As Boolean
arr() = Split(ansRng.Value, "|")
For i = 0 To UBound(arr)
If InStr(keyRng.Value, Trim(arr(i))) = 0 Then
found = True
CompareStrings = arr(i)
Exit Function
End If
Next i
If Not found Then
arr() = Split(keyRng.Value, "|")
For i = 0 To UBound(arr)
If InStr(ansRng.Value, Trim(arr(i))) = 0 Then
CompareStrings = arr(i)
Exit Function
End If
Next i
End If
End Function
Use this as before like below...
像以前一样使用它，如下所示......
=CompareStrings(A2,B2)
So the function will first compare all the string parts of B2 with A2 and if it finds any mismatch, it will return that part of string and if it doesn't find any mismatch, it will then compare all the parts of string in A2 with B2 and will return any mismatch part of string. So it will compare both ways.
因此，该函数将首先将 B2 的所有字符串部分与 A2 进行比较，如果发现任何不匹配，它将返回字符串的该部分，如果没有发现任何不匹配，则将 A2 中字符串的所有部分与B2 并将返回字符串的任何不匹配部分。所以它会比较两种方式。
