Excel VBA字符串评估 [英] Excel VBA String Evaluation

问题描述

我想知道是否可以使用一些VBA在一堆5char字符串中查找定义的模式?我已经研究了"instr"功能，但不确定是否会执行我需要的任务，该任务基本上是在字符串中查找如下所示的模式；

I'm wondering if it's possible to use some VBA to find defined patterns in a whole bunch of 5char strings? I've explored the "instr" function but I'm not sure if it will perform the task I require which is basically to find patterns in the string like the following;

ABABA
BCBCB
.....
EFEFE

或第1个字符与第3个字符&相同的任何此类模式5，第二个字符与字符4相同.

Or any such pattern where the 1st char is the same as chars 3 & 5, and the 2nd char is the same as char 4.

我们将不胜感激地收到任何帮助或指示.

Any help or direction would be gratefully received.

亲切的问候

吉姆

推荐答案

轮到我了:

Function findPattern(inputStr As String) As Variant()
Dim arr() As String
Dim i As Integer
arr = Split(inputStr)
ReDim arr2(UBound(arr)) As Variant
For i = LBound(arr) To UBound(arr)
    If Left(arr(i), 1) = Mid(arr(i), 3, 1) And _
        Left(arr(i), 1) = Mid(arr(i), 5, 1) And _
        Mid(arr(i), 2, 1) = Mid(arr(i), 4, 1) Then

        arr2(i) = "True"
    Else
        arr2(i) = "False"
    End If
    findPattern = arr2
Next
End Function

Sub trying()

    Dim t As String
    t = "ABABA BCBCB IOITU"
    arr = findPattern(t) 'returns an array {True,True,False}

    For x = 0 To 2
    Debug.Print arr(x)
    Next
End Sub

这假定每个字符串中包含多个单词.它返回一个true的false数组.

This assumes that you have multiple words in each string. It returns an array of true false.

编辑

要找到它，请使用此UDF:

To find it there are any patterns use this UDF:

Function findPattern(inputStr As String) As String
Dim i As Integer
For i = 5 To 1 Step -1
If Asc(Mid(inputStr, i, 1)) > 5 Then
    inputStr = Replace(inputStr, Mid(inputStr, i, 1), i)
End If
findPattern = inputStr
Next

End Function

它将在"ABABA"上返回12121

It will return 12121 on "ABABA"

您可以将其粘贴到工作簿的模块中，然后将其用作公式: = findPattern("A1")复制下来.然后在该列上排序，它将所有相似的图案与图案化最多的(11111)放到最少的图案(12345)一起.

You can paste this in a module in the workbook then use it like a formula: =findPattern("A1") Copy it down. Then sort on the column, it will place all like patterns together with the most patternized (11111) to the least (12345).

然后，您还可以在此列上过滤所需的任何模式.

Then you could also filter on this column for any pattern you desire.

这篇关于Excel VBA字符串评估的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！