Alphanumeric string format analysis

Question

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

For example, line 999aa45bbx will return 3N2A2N3A , that is.

• 3 numbers
• then 2 alpha
• on 2 numbers,
• 3 alpha.

I use this function to analyze the formats of insurance policy identification numbers. So far, I have found solutions on the Internet that extract alpha or numeric characters, but nothing that describes the format or order in which these characters exist in the source string.

Can anyone help?

Answer 1

A regular expression like this will do the job

• press alt f11 together to go to VBE
• Insert module
• copy and paste the code below
• press alt f11 together to return to Excel

then you can use the function (which also detects invalid rows) in Excel, i.e. in 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+|[az]+)" 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 

Answer 2

Old school iterating over all characters in a 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 

The following helper function is used. Please note that non-alphanumeric characters are identified by the letter "X". You can easily change this to suit your needs.

 Function CharType(s As String) As String If s Like "[Az]" 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: