☰

Excel - 以不同的顺序比较两个单元格中的名称

tag-icon • tag-icon vba microsoft-excel-2003 cells
Excel - 以不同的顺序比较两个单元格中的名称

我需要比较以下内容

Cell 1         John Peter henderson                          
Cell 2         peter John Henderson                
Result         Match

Cell 1         Anne jolie beuhler             
Cell 2         Jolie Anne
Result         NO MATCH

Cell 1         Kate spade lee
Cell 2         susan kate spade
Result         NO MATCH

我需要以任意顺序完美匹配名称。这是目前的代码：

function allIn(str1, str2) 
' check whether all elements of str1 occur in str2 
' and vice versa 
Dim l1, l2, ii As Integer 
Dim isfound As Boolean 
isfound = True 
l1 = Len(str1) 
l2 = Len(str2) 

If l1 < l2 Then ' look for all the elements of str1 in str2 

For ii = 1 To l1 

If InStr(1, str2, Mid(str1, ii, 1), vbTextCompare) <= 0 Then 
    isfound = False 
Exit For 
End If 
Next ii 
Else ' look for all the elements of str2 in str1 

For ii = 1 To l2

If InStr(1, str1, Mid(str2, ii, 1), vbTextCompare) <= 0 Then 
isfound = False 
Exit For 
End If 
Next ii 
End If 
allIn = isfound 
End Function

答案1

我已经很久没有用过 VBa 了，我无法测试这个，但是，如果它不能编译或者甚至不能完全按照要求工作，这应该能给你一个良好的开端。

' check whether all elements of str1 occur in str2
' and vice versa.
Function allIn(str1, str2) As Boolean

'first thing to check is if the 2 strings are the same length, if they're not then we know they are different
If Len(str1) <> Len(str2) Then
    allIn = False
End If

Dim isfound As Boolean
isfound = True

'Get the 1st string as array (split by white space)
Dim s1() As String
s1() = Split(str1)

'iterate through each array, word at a time
For Each element In s1

    If Not InStr(str2, element) Then
        isfound = False
    End If

'if it wasn't found, we can exit the loop immediately (no point finishing the test as it's already failed)
If (isfound = False) Then
    Exit For
End If

Next element

allIn = isfound
End Function

不过，我对这段代码有一些担忧，比如如果在 str1 或 str2 的开头或尾部有一个额外的空白会发生什么……但是，这应该可以解决你的问题（再次强调，未经测试）

您可能需要确保在进行比较等时字符串始终被修剪或小写，不确定它在 VBa 中如何工作。

相关内容