将日期格式更改为 yyyy-mm-dd

我有一个日期列，其中包含混合格式的日期。例如：

A
1990年3月21日
1990年3月21日

因此，基本上一列中有两种不同的格式：dd.mm.yyyy and mm/dd/yyyy。我正在尝试编写一个 VBA 脚本来将列中所有日期的格式更改为yyyy-mm-dd。这就是我到目前为止所得到的：

Sub changeFormat()

Dim rLastCell As Range
Dim cell As Range, i As Long
Dim LValue As String

i = 1

With ActiveWorkbook.Worksheets("Sheet1")
    Set rLastCell = .Range("A65536").End(xlUp)
    On Error Resume Next
    For Each cell In .Range("A1:A" & rLastCell.Row)
        LValue = Format(cell.Value, "yyyy-mm-dd")
        .Range("B" & i).Value = LValue
        i = i + 1
    Next cell
    On Error GoTo 0
End With

End Sub

我知道这不是一段优雅的代码，但我是 VBA 初学者，所以请原谅我。 该代码的问题在于，当我将 Format 函数中的参数从yyyy-mm-dd to dd/mm/yyyy它有效，但仅适用于格式的日期mm/dd/yyyy，和叶子dd.mm.yyyy未受影响。我将不胜感激任何建议。

更新：新答案

这是一个可以完成这项工作的解决方案！子例程包含一个执行替换的函数（该函数本身非常有用！）。运行子程序，A 列中所有出现的情况都将被修复。

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, "/") <> 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 

将此函数放在同一模块中：

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

怎么运行的：我有一个漂亮的 RegexReplace 函数，允许您使用正则表达式进行替换。 sub 几乎循环遍历您的 A 列，并对您提到的两种情况进行正则表达式替换。我首先使用 Instr() 的原因是确定它是否需要替换以及哪种替换。从技术上讲，您可以跳过此步骤，但对不需要它的单元进行替换确实成本高昂。最后，为了安全起见，我将单元格格式化为您的自定义日期格式，无论里面有什么。

如果您不熟悉正则表达式（参考：http://www.regular-expressions.info/ http://www.regular-expressions.info/），我使用的表达式是：

• () 中的每个项目都是捕获组 - 也就是您想要搞乱的东西
• \d 代表数字 [0-9]。
• {2} 表示 2 个，{4} 表示 4 个。为了安全起见，我在这里明确表示。
• 之前的 \ 。在第一次替换中需要因为“.”有特殊的意义。
• 在 VBA 正则表达式中，您可以使用 $ + no 来引用捕获组。的组。这就是我翻转这 3 个项目的顺序的方法。