您好！我想问您如何采用VBA对EXCEL表格中某日期列自动替...4

发布网友发布时间：2024-02-25 08:06

共3个回答

热心网友时间：2024-03-21 18:31

Sub test()
    Set x = CreateObject("vbscript.regexp")
    x.Global = 1
    x.Pattern = "(\d{4})[年\.\-/](\d{1,2})[月\.\-/](\d{1,2})"
    For Each y In Selection
        If x.test(y.Text) Then
        Set z = x.Execute(y.Text)(0)
        y.NumberFormatLocal = "@"
        y.Value = z.SubMatches(0) & Right("00" & z.SubMatches(1), 2) & Right("00" & z.SubMatches(2), 2)
        End If
    Next
End Sub

热心网友时间：2024-03-21 18:29

sub tihuan()
    With ActiveSheet
        j = .UsedRange.Rows.Count
        For i = 1 To j
            If IsDate(.Cells(i, 1)) Then
                .Cells(i, 1) = "'" & Format(.Cells(i, 1), "yyyymmdd")
            Else
                .Cells(i, 1) = "'" & Replace(.Cells(i, 1), ".", "")
            End If
        Next
    End With
end sub

为了防止单元格原来格式对数字的显示影响，代码中将输出前面转化成文本了（输出内容前加了单引号）。

热心网友时间：2024-03-21 18:30

=TEXT(A1,"yyyymmdd")