誰能幫我用公式來比較 excel 中的兩個字串，使用 excel 公式或 vba 宏
例子 ：-
str 1 : abcd def
str 2 : def abcd
如果我們比較 str 1 和 str 2 那么它應該回傳 true
提前致謝
str 1 : abcd def
str 2 : def abcd
=str1 = str2
uj5u.com熱心網友回復：
我假設您想比較兩個字串的單個單詞，但順序不限。Excel 或 VBA 中沒有內置函式。
以下函式將兩個字串拆分為單個單詞（使用split-function）并檢查第一個字串的每個單詞是否也存在于第二個字串中。我添加了一個小的正則運算式代碼來去除兩個字串中的多個空格或制表符，如果您確定單詞之間始終只有一個空格，則可以洗掉這些行
Function compareStrings(string1 As String, string2 As String) As Boolean
' Replace multiple spaces and tabs
Dim regX As Object
Set regX = CreateObject("VBScript.RegExp")
regX.Pattern = "\s{2,}"
regX.Global = True
string1 = regX.Replace(string1, " ")
string2 = regX.Replace(string2, " ")
' Split both strings into single words
Dim string1Tokens() As String, string2Tokens() As String
string1Tokens = Split(string1, " ")
string2Tokens = Split(string2, " ")
' If we have a different number of words we don't need to continue.
If UBound(string1Tokens) <> UBound(string2Tokens) Then Exit Function
Dim i1 As Long, i2 As Long
For i1 = LBound(string1Tokens) To UBound(string1Tokens)
Dim wordFound As Boolean
wordFound = False
For i2 = LBound(string2Tokens) To UBound(string1Tokens)
If string1Tokens(i1) = string2Tokens(i2) Then
wordFound = True
Exit For
End If
Next
' Word of first string was not found.
If Not wordFound Then Exit Function
Next
' All words where found
compareStrings = True
End Function
uj5u.com熱心網友回復：
另一種方法是對每個字串的字符代碼求和并比較它們的值。首先檢查兩個字串的長度，如果它們不相同，則可以避免這一切。
老實說，我不知道這有什么用。:)
Const S1 As String = "abcd def"
Const S2 As String = "def abcd"
Debug.Print WordSum(S1) = WordSum(S2)
'True
Function WordSum(ByVal word As String) As Long
Dim w As Variant
For Each w In Split(word)
WordSum = WordSum Asc(w)
Next
End Function
uj5u.com熱心網友回復：
正如 Kostas 在評論中提到的那樣，拆分-排序-連接字串可以讓您比較兩個字串，而不管組成這些字串的單詞順序如何：
Function name_compare(n1 As String, n2 As String) As Boolean
n1 = Join(sort_array(Split(UCase(n1), " ")), " ")
n2 = Join(sort_array(Split(UCase(n2), " ")), " ")
name_compare = (n1 = n2)
End Function
Function sort_array(arr)
Dim i As Long, j As Long, temp As String
For i = LBound(arr) To UBound(arr) - 1
For j = i 1 To UBound(arr)
If arr(i) > arr(j) Then
temp = arr(j)
arr(j) = arr(i)
arr(i) = temp
End If
Next j
Next i
sort_array = arr
End Function
您可以像這樣使用此功能：
Const name1 As String = "abcd def"
Const name2 As String = "def abcd"
Debug.Print name_compare(name1, name2)
結果：
True
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/535464.html
標籤：擅长VBAexcel公式