Reformat multiple dates in string using EXCEL VBA REGEX

Question

I have an application which outputs queries in the form of copying text to the clipboard.

I've come up with parsing the text into arrays which I then populate back to excel.

the source string contains multiple instances of dates in the format "dd.mm.yyyy" I've been using find/replace on the output column to change this to "dd/mm/yyyy". I was wondering if it would be possible/faster to replace these in the string before parsing to the array by using regex.

strPattern = "(0?[1-9]|[12][0-9]|3[01])[\.](0?[1-9]|1[012])[\.]\d{4}"
strReplace = "$dd\/dd\/dddd"

    With regEx
        .Global = True
        .MultiLine = False
        .IgnoreCase = False
        .Pattern = strPattern
    End With

    If regEx.test(sString) Then
        sString = regEx.Replace(sString, strReplace)
    End If

the end result is not what I was hoping for yet. I think the format of the 'strReplace' is wrong but I don't know regex well enough to fix it. the above was achieved by (too many) hours of web searches.

Thanks for any help.

Try strReplace = "$1/$2/$3" BTW, you do not need to test the string for a match. If there is no match, the string will not be modified. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented May 17, 2018 at 14:25
Yes, because you have no capturing group around \d{4}. Use "(0?[1-9]|[12][0-9]|3[01])\.(0?[1-9]|1[012])\.(\d{4})" pattern and strReplace = "$1/$2/$3" — Wiktor Stribiżew
– Wiktor Stribiżew, Commented May 17, 2018 at 14:34

Wiktor Stribiżew · Accepted Answer · 2018-05-17 14:36:55Z

1

Use

strPattern = "(0?[1-9]|[12][0-9]|3[01])\.(0?[1-9]|1[012])\.(\d{4})"
strReplace = "$1/$2/$3"

With regEx
    .Global = True
    .MultiLine = False
    .IgnoreCase = False
    .Pattern = strPattern
End With

sString = regEx.Replace(sString, strReplace)

Note:

You can refer to a capturing group value using $n placeholder from the replacement string
You do not need to use [\.], it is the same as \. (or [.])
You do not need to test the string for a match. If there is no match, the string will not be modified.

A hint: if you want to match these strings as "whole word", use word boundaries \b:

strPattern = "\b(0?[1-9]|[12][0-9]|3[01])\.(0?[1-9]|1[012])\.(\d{4})\b"

Now, the date-like string will only be found when not enclosed with letters, digits or _ chars.

answered May 17, 2018 at 14:36

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tsuimark Over a year ago

many thanks for the quick response and helpful hints. problem solved.

Collectives™ on Stack Overflow

Reformat multiple dates in string using EXCEL VBA REGEX

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related