vba 分析字母数字字符串的格式

Analyse format of alpha-numeric string

提问by regulus

I'm trying to write a function that takes in a string, parses it, and returns another string that summarizes the number of consecutive alpha or numeric characters in the original string.

我正在尝试编写一个函数，该函数接受一个字符串，解析它，然后返回另一个字符串，该字符串总结了原始字符串中连续字母或数字字符的数量。

For example, the string 999aa45bbxwould return 3N2A2N3A, i.e.

例如，字符串999aa45bbx将返回3N2A2N3A，即

• 3 numbers,
• followed by 2 alpha,
• by 2 numbers,
• by 3 alpha.

• 3个数字，
• 然后是 2 个 alpha，
• 按 2 个数字，
• 由 3 个阿尔法。

I'm using the function to analyze formats of insurance policy ID numbers. So far, I've found solutions online that extract either alpha or numeric characters, but nothing that describes the format or order in which these characters exist in the original string.

我正在使用该功能来分析保险单 ID 号的格式。到目前为止，我在网上找到了提取字母或数字字符的解决方案，但没有描述这些字符在原始字符串中存在的格式或顺序。

Can anyone help?

任何人都可以帮忙吗？

回答by brettdj

A regexp like this will do the job

像这样的正则表达式将完成这项工作

• press altf11together to go the VBE
• Insert Module
• copy and paste the code below
• press altf11together to go back to Excel

• 同时按alt f11进入 VBE
• 插入模块
• 复制并粘贴下面的代码
• 同时按alt f11返回 Excel

then you can use the function (which also detects invalid strings) within Excel, ie in B1
=AlphaNumeric(A1)

然后您可以在 Excel 中使用该函数（它还检测无效字符串），即在 B1 中
=AlphaNumeric(A1)

Function AlphaNumeric(strIn As String) As String
    Dim objRegex As Object
    Dim objRegMC As Object
    Dim objRegM As Object
    Dim strOut As String
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Global = True
        .ignorecase = True
        .Pattern = "[^\w]"
        If .test(strIn) Then
            AlphaNumeric = "One or more characters is invalid"
        Else
            .Pattern = "(\d+|[a-z]+)"
            Set objRegMC = .Execute(strIn)
            For Each objRegM In objRegMC
                strOut = strOut & (objRegM.Length & IIf(IsNumeric(objRegM), "N", "A"))
            Next
            AlphaNumeric = strOut
        End If
    End With
End Function

回答by Jean-Fran?ois Corbett

Old school, looping through all characters in the string:

老派，循环遍历字符串中的所有字符：

Function IdentifyCharacterSequences(s As String) As String
    Dim i As Long
    Dim charCounter As Long
    Dim currentCharType As String
    Dim sOut As String

    sOut = ""
    charCounter = 1
    currentCharType = CharType(Mid(s, 1, 1))

    For i = 2 To Len(s)
        If (Not CharType(Mid(s, i, 1)) = currentCharType) Or (i = Len(s)) Then
            sOut = sOut & charCounter & currentCharType
            currentCharType = CharType(Mid(s, i, 1))
            charCounter = 1
        Else
            charCounter = charCounter + 1
        End If
    Next i

    IdentifyCharacterSequences = sOut
End Function

This uses the following helper function. Note that non-alphanumeric characters are identified using the letter "X". You can easily modify this to suit your purposes.

这使用以下帮助函数。请注意，非字母数字字符使用字母“X”进行标识。您可以轻松修改它以适合您的目的。

Function CharType(s As String) As String
    If s Like "[A-z]" Then
        CharType = "A"
    ElseIf s Like "[0-9]" Then
        CharType = "N"
    Else
        CharType = "X"
        'Or raise an error if non-alphanumerical chars are unacceptable.
    End If
End Function

Usage example:

用法示例：