CODEWITHSUNDEEP
vbams-word
Ahmed Enzea
Ahmed Enzea

Reputation: 55

VBA starts with or ends with a special character

I want to see if a string starts with or ends with a special character

testString("#Testing")     Returns: true
testString("Testing\")     Returns: true
testString("#Testing)")    Returns: true
testString("Tes#ting~")    Returns: true
testString("Tes#ting")     Returns: false
testString("Testing")      Returns: false

The idea is to use a regular expression

Dim rg As Variant
Set rg = CreateObject("VBScript.RegExp")

rg.Pattern = ""

returnFunc = rg.test(paramString)

However, I am not sure how to create a regular expression to check symbols.

All alternative solutions are welcome

So if it starts or ends with anything other than [a-Z][0-9]

Upvotes: 0

Views: 1717

Answers (3)

Slai
Slai

Reputation: 22876

To check if string does not start and end with alphanumeric characters using the VB Like operator:

If Not "#Testing" Like "[0-9A-Za-z]*[0-9A-Za-z]" Then MsgBox True

If the string might be less than 2 characters:

If string Like "[!0-9A-Za-z]*" Or string Like "*[!0-9A-Za-z]" Then MsgBox True

Upvotes: 0

user9182
user9182

Reputation:

If you don’t need to change your definition of special characters for different languages or other reasons then you can simply checking the first and last character against a list of valid characters would work.

Public Function testString(text As String)        
    testString = isCharAlphaNumeric(Left(text, 1)) Or isCharAlphaNumeric(Right(text, 1))    
End Function

Public Function isCharAlphaNumeric(char)
    Const valid As String = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"

    isCharAlphaNumeric = InStr(valid, char) = 0
End Function

Public Sub test()    
    Debug.Print testString("#Testing")   '  Returns: true
    Debug.Print testString("Testing\")   '  Returns: true
    Debug.Print testString("#Testing)")  '  Returns: true
    Debug.Print testString("Tes#ting~")  '  Returns: true
    Debug.Print testString("Tes#ting")   '  Returns: false
    Debug.Print testString("Testing")    '  Returns: false    
End Sub

Upvotes: 0

Roemer
Roemer

Reputation: 1261

Function test(x)
    Dim rg As Variant
    Set rg = CreateObject("VBScript.RegExp")

    rg.Pattern = "^([^A-Za-z0-9].*|.*[^A-Za-z0-9])$"

    test = rg.test(x)
End Function

Sub hoi()
    Debug.Print test("#Testing")
    Debug.Print test("Testing\")
    Debug.Print test("#Testing)")
    Debug.Print test("Tes#ting~")
    Debug.Print test("Tes#ting")
    Debug.Print test("Testing")
End Sub

Upvotes: 1

Related Questions