Regex in Excel VBA Special Characters and Embedded Spaces

Vote count:

0

I have to parse a huge file, but one of the values is causing me a lot of grief.

It is a fixed length field of six characters. The description of the allowable values is:

Left justified; space filled. Cannot contain special characters or embedded spaces. If data is unavailable, space filled.

What I have attempted so far is to check:

If Code = "      " Then
    MsgBox "Code is Space Filled." 

This will check if it is all space filled, which is ok.

Next I check if there is any special characters using the following function:

With ObjRegex
    .Global = True
    .Pattern = "[^a-zA-Z0-9\s]+"
    StripNonAlpha = .Replace(Replace(TextToReplace, "-", Chr(32)),     
End With

I can compare two strings, the original code and the stripped of special characters one. If they don't match then it contains a special character and is not valid.

It is the spaces that are causing me issues. I have to check for left aligned, no embedded spaces and trailing spaces (aka left justified).

I have tried a few variations of the above function but to no avail.

e.g. (wrong):

(^\sa-zA-Z0-9\sa-zA-Z0-9)+

I would appreciate any pointers, and if regex is the wrong way to go I'm happy to abandon them.

asked 46 secs ago

RossC

562

Regex in Excel VBA Special Characters and Embedded Spaces