I've got some data in excel where one column contains text-values of the form

vr, 1-07-2022

do, 21-07-2022

as you can see, the day numerical does not contain leading zeroes. I need to replace the date strings in this column so that all days are written as double digits, so single-digit days should get a leading zero.

I've tried to do this using regex, but i'm having trouble to get backreferencing to work.

I'm using the following macro:

Function FindReplaceRegex(rng As Range, reg_exp As String, replace As String)
    Set myRegExp = New RegExp
    myRegExp.IgnoreCase = False
    myRegExp.Global = True
    myRegExp.Pattern = reg_exp
    
    FindReplaceRegex = myRegExp.replace(rng.Value, replace)
End Function

i've created the following regex to recognize dates with single-digit days:

"\s{1}\d{1}\W{1}\d{2}\W{1}\d{4}"

I need to replace the entire string that fits the regex with the first space replaced by a space + 0. So i've tried it like this:

FindReplaceRegex(C22, "\s{1}\1\d{1}\W{1}\d{2}\W{1}\d{4}", " 0$1")

where i inserted a "\1" after the space in the regex to create a reference to the entire regex starting with \d{1}... and then tried to make the replace value call it using $1.

It doesn't work.

For the input vr, 1-07-2022 i need an output of vr, 01-07-2022 and for the input do, 21-07-2022 i need the output to remain unchanged do, 21-07-2022.

can someone tell me what i'm doing wrong?