UPDATED: NEW ANSWER
Here is a solution that will do the job! The sub routine includes a function that does the replacement (the function itself is really useful!). Run the sub and all occurances in column A will be fixed.
Sub FixDates()
Dim cell As range
Dim lastRow As Long
lastRow = range("A" & Rows.count).End(xlUp).Row
For Each cell In range("A1:A" & lastRow)
If InStr(cell.Value, ".") <> 0 Then
cell.Value = RegexReplace(cell.Value, _
"(\d{2})\.(\d{2})\.(\d{4})", "$3-$2-$1")
End If
If InStr(cell.Value, "https://stackoverflow.com/") <> 0 Then
cell.Value = RegexReplace(cell.Value, _
"(\d{2})/(\d{2})/(\d{4})", "$3-$1-$2")
End If
cell.NumberFormat = "yyyy-mm-d;@"
Next
End Sub
Place this function in the same module:
Function RegexReplace(ByVal text As String, _
ByVal replace_what As String, _
ByVal replace_with As String) As String
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.pattern = replace_what
RE.Global = True
RegexReplace = RE.Replace(text, replace_with)
End Function
How it works: I have a nifty RegexReplace function that allows you to do replace using regular expressions. The sub mearly loops through your A column and does a regex replace for those 2 cases you mentioned. The reason I use an Instr() first is to determain if it needs the replacement, and which kind. You could technically skip this but doing replace on cells that don’t need it is really costly. At the end I format the cell to your custom date format regardless of what’s inside for safe measure.
In case you aren’t familiar with Regex (for ref: http://www.regular-expressions.info/), the expression I am using is:
- Each item in () are capture groups – aka, the stuff you want to mess with
- \d stands for a number [0-9].
- {2} means 2 of, and {4} mean 4 of. I have been explicit here for safety.
- The \ before the . in the first replace is needed since “.” has special meaning.
- In VBA regex, you refer to capture groups by using $ + no. of group. This is how I flip the order of the 3 items.