Regex Pattern Matching

Hi folks,

I'm well acquainted with pattern matching using the str functions in VBA.

I'm wondering, however, about the viability of using regex matches instead when running through multiple ranges for the same pattern.

I'm VERY green with regex so please forgive my ignorance - if I wanted to match the following, how would it be done?

-Length must be 10 characters
- First 4 characters are letters only (no numbers)
- IF the length is not 10 digits, insert a 0 AFTER the first 4 letters for every missing digit

i.e. if I had ABCD01234 -> ABCD001234
DCBA87623 -> DCBA087623
WXYZ2354 -> WXYZ002354

What would this look like?

Thanks in advance.

Hello visualnotsobasic,

Here is a macro that checks the string for 4 alpha characters (case is ignored) and then must be followed by 1 to 6 digits. Additional leading zeros are added if there are less than 6 digits.

Please Login or Register  to view this content.

Hi Leith,

Sorry for my late reply!
It looks fantastic, but I for some reason can not figure out how to get it to run on a specific range.

Maybe just to clarify a bit more - if I have a named range, how do I run the regex match and replace it with the proper string on each cell?

I apologize for my ignorance. I'm very new to this and I've been fooling around with your code for a few hours, but I can't seem to get it to do anything.

Hello visualnotsobasic,

Since you did not say how many cells there were, I optimized the code for speed and a large data set. Add a new standard VBA module to your workbook then copy and paste the code below into it. Be sure to change the name of the named range in the macro "Test" before you run the macro.

Please Login or Register  to view this content.

Absolutely brilliant on the parts of both of you, thank you!

As an aside, would you mind explaining how that replace function works?

Hello visualnotsobasic,

The RegEx replace method, replaces the old text with the new text. A shorthand for referencing a submatch is to use the dollar sign prefix in front of submatch's position. Thus, "$1"indicates the first submatch in the pattern, the four letters. This is concatenated with the second submatch, the numbers, after the numbers are converted from a string to a value and back to string with the required number of zeroes added in.