vba 比较excel vba中的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15365250/
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
Compare strings in excel vba
提问by hardikudeshi
I have a bunch of strings comprising of characters "A","B"..."Z" (and no others). A typical string looks like ABZYC. The strings are given to me in pairs like ABC,ABDC. The strings are comparable if one string is contained in the other (i.e either one of the two strings contain all the alphabets of the other). The order in which the string appears don't matter.
我有一堆由字符“A”、“B”...“Z”（没有其他字符）组成的字符串。一个典型的字符串看起来像ABZYC. 字符串是成对给我的，比如ABC, ABDC。如果一个字符串包含在另一个字符串中（即两个字符串中的一个包含另一个的所有字母表），则这些字符串是可比较的。字符串出现的顺序无关紧要。
Is there any direct function in excel vba which does this sort of comparison?
excel vba 中是否有任何直接函数可以进行这种比较？
Examples:ACBD,AC- MatchACBD,CA- MatchACBD,ADB- MatchAC,ABCD- MatchABC, ABD- No Match
示例：ACBD, AC- MatchACBD, CA- MatchACBD, ADB- MatchAC, ABCD- MatchABC, ABD-No Match
回答by Floris
Add the following function in a module in your workbook:
在工作簿的模块中添加以下函数：
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
Now you can call this from another place in your code, using result = inStr("ABD", "BAD")- or from the spreadsheet itself. On the spreadsheet you would type =allIn(A3, B6)to compare strings in cells A3and B6.
现在，您可以使用result = inStr("ABD", "BAD")- 或从电子表格本身从代码中的另一个位置调用它。在电子表格上，您将键入=allIn(A3, B6)以比较单元格A3和B6.
Here is what happens when I did that (I entered =allIn(A1, B1)in cell C1, then dragged the formula to the next four rows):
这是我这样做时发生的情况（我=allIn(A1, B1)在 cell 中输入C1，然后将公式拖到接下来的四行）：
I believe that solves your problem.
我相信这可以解决您的问题。
EDIT: I just noticed @Philip's comment to your question - I appear to have implemented his suggestion although I had not seen it when I started to compose it... But here's a tip of the hat all the same!
编辑：我刚刚注意到@Philip 对您的问题的评论 - 我似乎已经实施了他的建议，尽管我在开始撰写它时还没有看到它......但这里还是一个小窍门！
回答by Our Man in Bananas
If you want a Formula solution, a user called Schielrnon the Mr Excelforum site came up with this sublime masterpiece(using ARRAY FORMULAS)
如果你想要一个公式解决方案，Excel 先生论坛站点上一个叫Schielrn的用户提出了这个崇高的杰作（使用ARRAY FORMULAS）
Or, if you want a VBA, try this...
或者，如果你想要一个 VBA，试试这个......
Sub compare()
Dim iIndx As Integer
Dim str1 As String
Dim str2 As String
Dim sLetter As String
Dim bFound As Boolean
Range("A1").Select
bFound = False
Do
str1 = VBA.Trim(ActiveCell.Text)
str2 = VBA.Trim(ActiveCell.Offset(0, 1).Text)
For iIndx = 1 To Len(str1)
If VBA.InStr(str2, VBA.Mid(str1, iIndx, 1)) <> "" Then
' found it
bFound = True
Else
bFound = False
exit for
End If
Next
If bFound = False Then
' check the other way!
For iIndx = 1 To Len(str2)
If VBA.InStr(str1, VBA.Mid(str2, iIndx, 1)) <> "" Then
' found it
bFound = True
Else
bFound = False
exit for
End If
Next
End If
If bFound = True Then ActiveCell.Offset(0, 2).Value = "MATCHED!"
ActiveCell.Offset(1, 0).Select
Loop While Not ActiveCell.Offset(1, 0).Text = ""
End Sub
回答by cardmagik
INSTR will find a substring in a string:
INSTR 将在字符串中查找子字符串：
Typical_String = "ABZYC"
if instr(Typical_String,"ABC") > 0 then
回答by Alec
I missread the post!
我看错了帖子！
Use function EXACT
使用功能 EXACT
Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences.
比较两个文本字符串，如果完全相同则返回 TRUE，否则返回 FALSE。EXACT 区分大小写，但忽略格式差异。
I usually add the function UPPER ie:
我通常添加功能 UPPER ie：
A1 = Some Place
B1 = some place
with
和
=EXACT(UPPER(A1),UPPER(B1)) = EXACT(SOME PLACE, SOME PLACE) = TRUE
Without UPPER
没有 UPPER
=EXACT(A1,B1) = EXACT(Some Place, some place) = FALSE
