excel - 如何用其他值替换单元格中的前几个空格

Question

例如，我有显示为“01 01 1970 12:00”的日期值。由于日期使用空格而不是“-”或“/”，因此 Excel 不会将其识别为日期值。
使用Range = Replace(Range, " ", "/", 1, 2)或者Range = Replace(Range, " ", "/")返回类型不匹配错误
使用Range.Replace what:=" ", replacement:="/", lookat:=xlPart有效，但它也替换了最后一个空格并将值变为我不想要的“01/01/1970/12:00”。它应该以日期格式返回。

Answer 1

替换日期分隔符
Excel 公式 ( SUBSTITUTE )

Sub ReplaceDateSeparator()

    Const FirstRowAddress As String = "C2:D2"
    Const SearchSeparator As String = " "
    Const ReplaceSeparator As String = "/"
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim rg As Range
    
    With ws.Range(FirstRowAddress)
        Dim lCell As Range: Set lCell = .Resize(ws.Rows.Count - .Row + 1) _
            .Find("*", , xlFormulas, , xlByRows, xlPrevious)
        If lCell Is Nothing Then Exit Sub
        Set rg = .Resize(lCell.Row - .Row + 1)
    End With

    rg.Value = ws.Evaluate("=SUBSTITUTE(SUBSTITUTE(" & rg.Address _
        & ",""" & SearchSeparator & """,""" & ReplaceSeparator _
        & """,1),""" & SearchSeparator & """,""" & ReplaceSeparator & """,1)")

End Sub

VBA ( Split/Join )

Sub ReplaceDateSeparatorVbaTEST()

    Const FirstRowAddress As String = "C2:D2"
    Const SearchSeparator As String = " "
    Const ReplaceSeparator As String = "/"
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim rg As Range
    
    With ws.Range(FirstRowAddress)
        Dim lCell As Range: Set lCell = .Resize(ws.Rows.Count - .Row + 1) _
            .Find("*", , xlFormulas, , xlByRows, xlPrevious)
        If lCell Is Nothing Then Exit Sub
        Set rg = .Resize(lCell.Row - .Row + 1)
    End With

    ReplaceDateSeparatorVba rg, SearchSeparator, ReplaceSeparator

End Sub

Sub ReplaceDateSeparatorVba( _
        ByVal rg As Range, _
        Optional ByVal SearchSeparator As String = " ", _
        Optional ByVal ReplaceSeparator As String = "/")
    
    Dim rCount As Long: rCount = rg.Rows.Count
    Dim cCount As Long: cCount = rg.Columns.Count
    
    Dim Data As Variant
    
    If rCount + cCount = 2 Then ' one cell
        ReDim Data(1 To 1, 1 To 1): Data(1, 1) = rg.Value
    Else ' multiple cells
        Data = rg.Value
    End If
    
    Dim Arr() As String
    Dim cValue As Variant
    Dim r As Long
    Dim c As Long
    
    For r = 1 To rCount
        For c = 1 To cCount
            cValue = Data(r, c)
            If Not IsError(cValue) Then ' exclude error values
                Arr = Split(Data(r, c), SearchSeparator, 3)
                Data(r, c) = Join(Arr, ReplaceSeparator)
            End If
        Next c
    Next r
    
    rg.Value = Data
    
End Sub