如何比较Excel单元格中的文本,看看是否找到相同的单词? [英] How do I compare text in Excel cells to see if same words are found?
问题描述
例如:
单元格A1:狗,猫,兔,老鼠,狮,熊,老虎
cell A2:香肠,腌菜,狗,猫,大象,面包
结果: dog,cat
结果也可以是一个数字(例如2),如果这更容易。
非常感谢!
我认为您最适合使用VBA,您可以将其部署为用户定义的函数
(UDF)
然后调用Excel中的函数,如
= DupeWord(A1,A2)
查找A1和A2之间的任何匹配
Usr定义的函数
/ p>
功能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 =拆分(替换(str2,,vbNullString ),,)
错误GoTo strExit
对于lngCnt = LBound(vArr1)到UBound(vArr1)
vTest = Application.Match(vArr1(lngCnt),vArr2 ,0)
如果不是IsError(vTest)则DupeWord = DupeWord& vArr1(lngCnt)& ,
Next lngCnt
如果Len(DupeWord)> 0然后
DupeWord =左$(DupeWord,Len(DupeWord) - 2)
Else
strExit:
DupeWord =No Matches!
如果
结束函数
VBA内使用
Sub test()
MsgBox DupeWord(狗,猫,兔,老鼠,狮子,熊,老虎,香肠,腌菜,狗,猫,大象,面包)
End Sub
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:
cell A1: dog, cat, rabbit, mouse, lion, bear, tiger
cell A2: sausage, pickle, dog, cat, elephant, bread
result: dog, cat
The result could also be a number (e.g. 2) if that is easier. Many thanks!
I think you would be best served using VBA which you could then deploy as a User Defined Function
(UDF)
- hold down altf11 to go to the VBE
- Insert ... Module
- copy and paste in the code below
- hold down altf11 to go back to Excel
Then call the function in Excel such as
=DupeWord(A1,A2)
to find any matches between A1 and A2
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
Sub test()
MsgBox DupeWord("dog, cat, rabbit, mouse, lion, bear, tiger", "sausage, pickle, dog, cat, elephant, bread")
End Sub
这篇关于如何比较Excel单元格中的文本,看看是否找到相同的单词?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!