Break String into individual elements and test for type of Character - NUM - LETTER - SPECIAL - Excel VBA

Question

I need to figure out how I can test each character in the string to see if it is a number/letter/special character.

My question is, how can I break a string and test each individual character to see if the character is a number/letter/special character

Eg:

var = 1S@

Result1 = Num
Result2 = Alpha
Result3 = Special

@TimBiegeleisen You mean like use RegEx to search the string? I was thinking more of testing the characters via `CODE` or just `If/THEN`, but that might work. — FreeSoftwareServers, Apr 04 '19 at 05:08
Do you mean that user provides string - which is supposed to be used as a regex used for searching? For example I type ^stack\w*$ and then it finds all words that start with "stack"? — matvs, Apr 04 '19 at 12:34
@MateuszŚwiątkowski that is the goal, except the user wouldn't be required to use those special characters, it would be something like `Call RegexSearch(String2FindExample)` — FreeSoftwareServers, Apr 04 '19 at 23:22

score 0 · Answer 1 · answered Apr 04 '19 at 13:58

0

If you mean

escaping user input that is to be treated as a literal string within a regular expression—that would otherwise be mistaken for a special character.

Then you can replace it with given regular expression:

/[.*+?^${}()|[\]\\]/g

answered Apr 04 '19 at 13:58

matvs

1,763
16
26

I don't think you understand the question I will edit now – FreeSoftwareServers Apr 04 '19 at 23:17

score 0 · Accepted Answer · answered Apr 05 '19 at 09:49

So I got it to work by combining a few different posts on SO. This code breaks the string in an array and then checks each one for num/alpha/special and has a special case for *.

-

Sub test()

'''Special Character Section'''
Dim special_charArr() As String
Dim special_char As String

special_char = "!,@,#,$,%,^,&,*,+,/,\,;,:"
special_charArr() = Split(special_char, ",")
'''Special Character Section'''

'''Alpha Section'''
Dim regexp As Object
Set regexp = CreateObject("vbscript.regexp")

Dim strPattern As String
strPattern = "([a-z])"

With regexp
    .ignoreCase = True
    .Pattern = strPattern
End With
'''Alpha Section'''

Dim buff() As String
my_string = "t3s!*"
ReDim buff(Len(my_string) - 1)
For i = 1 To Len(my_string)
    buff(i - 1) = Mid$(my_string, i, 1)
    char = buff(i - 1)
    If IsNumeric(char) = True Then
        MsgBox char & " = Number"
    End If
    For Each Key In special_charArr
     special = InStr(char, Key)
      If special = 1 Then
        If Key <> "*" Then
            MsgBox char & " = Special NOT *"
        Else
            MsgBox char & " = *"
        End If
      End If
    Next
    If regexp.test(char) Then
        MsgBox char & " = Alpha"
    End If
Next
End Sub

Break String into individual elements and test for type of Character - NUM - LETTER - SPECIAL - Excel VBA

2 Answers2