vba 如何比较 Excel 单元格中的文本以查看是否找到相同的单词？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12069890/
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 do I compare text in Excel cells to see if same words are found?
提问by dgBP
I have several rows of Excel cells which contain a string of words, all separated by commas. I want to compare each cell with another cell to check if any of the words match/are duplicates. For example:
我有几行 Excel 单元格，其中包含一串单词，全部用逗号分隔。我想将每个单元格与另一个单元格进行比较，以检查是否有任何单词匹配/重复。例如：
cell A1: dog, cat, rabbit, mouse, lion, bear, tiger
cell A2: sausage, pickle, dog, cat, elephant, bread
单元格 A1：dog, cat, rabbit, mouse, lion, bear, tiger
单元格 A2：sausage, pickle, dog, cat, elephant, bread
result: dog, cat
结果： dog, cat
The result could also be a number (e.g. 2) if that is easier. Many thanks!
如果更容易，结果也可以是一个数字（例如 2）。非常感谢！
回答by lukaswelte
回答by brettdj
I think you would be best served using VBA which you could then deploy as a User Defined Function(UDF)
我认为您最好使用 VBA，然后您可以将其部署为User Defined Function（UDF）
- hold down altf11to go to the VBE
- Insert ... Module
- copy and paste in the code below
- hold down altf11to go back to Excel
Then call the function in Excel such as=DupeWord(A1,A2)to find any matches between A1 and A2
然后在 Excel 中调用该函数，例如=DupeWord(A1,A2)查找 A1 和 A2 之间的任何匹配项
Usr Defined Function
Usr Defined Function
Function DupeWord(str1 As String, str2 As String) As String
Dim vArr1
Dim vArr2
Dim vTest
Dim lngCnt As Long
vArr1 = Split(Replace(str1, " ", vbNullString), ",")
vArr2 = Split(Replace(str2, " ", vbNullString), ",")
On Error GoTo strExit
For lngCnt = LBound(vArr1) To UBound(vArr1)
vTest = Application.Match(vArr1(lngCnt), vArr2, 0)
If Not IsError(vTest) Then DupeWord = DupeWord & vArr1(lngCnt) & ", "
Next lngCnt
If Len(DupeWord) > 0 Then
DupeWord = Left$(DupeWord, Len(DupeWord) - 2)
Else
strExit:
DupeWord = "No Matches!"
End If
End Function
Use inside VBA
Use inside VBA
Sub test()
MsgBox DupeWord("dog, cat, rabbit, mouse, lion, bear, tiger", "sausage, pickle, dog, cat, elephant, bread")
End Sub
