VBA Express : Excel - Find the pattern of string

Excel

Find the pattern of string

Ease of Use

Easy

Version tested with

2010

Submitted by:

erashok

Description:

Create a pattern of the string in specified cell

Discussion:

This macro is used to exact the pattern of the string, includes numbers,alphabets and special char in a particular cell

Code:

instructions for use

			Function CreateReg(s As String) As String 
    Dim i As Long 
    Dim x As Long 
    Dim charCounter As Long 
    Dim currentCharType As String 
    Dim sOut As String 
     
    sOut = "" 
    charCounter = 1 
    currentCharType = CharType(Mid(s, 1, 1)) 
    x = Len(s) + 1 
     
    For i = 2 To x 
        If (Not CharType(Mid(s, i, 1)) = currentCharType) Or (i = x) Then 
            sOut = sOut & currentCharType & "{" & charCounter & "}" 
            currentCharType = CharType(Mid(s, i, 1)) 
            charCounter = 1 
        Else 
            charCounter = charCounter + 1 
        End If 
    Next i 
     
    CreateReg = sOut 
     
End Function 
 
Function CharType(s As String) As String 
    If s Like "[A-z]" Then 
        CharType = "[a-z]" 
    ElseIf s Like "[0-9]" Then 
        CharType = "[0-9]" 
    ElseIf s Like "*-*" Then 
        CharType = "[-]" 
    ElseIf s Like "* *" Then 
        CharType = "[ ]" 
    ElseIf s Like "*/*" Then 
        CharType = "[//]" 
    ElseIf s Like "*.*" Then 
        CharType = "[.]" 
    ElseIf s Like "*(*" Then 
        CharType = "[(]" 
    ElseIf s Like "*)*" Then 
        CharType = "[)]" 
    ElseIf s Like "**" Then 
        CharType = "[\]" 
    ElseIf s Like "*+*" Then 
        CharType = "[+]" 
    ElseIf s Like "*<*" Then 
        CharType = "[<]" 
    ElseIf s Like "*>*" Then 
        CharType = "[>]" 
    ElseIf s Like "*~*" Then 
        CharType = "[~]" 
    Else 
        CharType = "X" 
    End If 
End Function

How to use:

Copy above code.
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Right-click desired file on left (in bold).
Choose Insert -> Module.
Paste code into the right pane.
Press Alt + Q to close the VBE.
Save workbook(Macro-Enabled) before any other changes.

Test the code:

In a cell A1, enter the string to exact the pattern.
In A2, enter formula as =CreateReg(A1), where A1 can be a cell address, or the text you want to exact the pattern.
It will return the pattern of string in A1.

Sample File:

Pattern.zip 12.46KB

Approved by mdmackillop

This entry has been viewed 74 times.