How to replace first few spaces in cell with other values

user_13819830 0
Viewed 46

I have date values which are shown as "01 01 1970 12:00" for example. Since the date uses spaces and not "-" or "/", excel does not recognize it as a date value.

Using Range = Replace(Range, " ", "/", 1, 2) or Range = Replace(Range, " ", "/") returns a type mismatch error

Using Range.Replace what:=" ", replacement:="/", lookat:=xlPart works, but it also replaces the last space and turns the value to "01/01/1970/12:00" which i do not want. It is supposed to return as a date format.

vba excel
2 Answers

Replace Date Separator

Excel Formula (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
user_98140690

Fist value of Funtion Replace is String

So You must change Range to value of Cell:

Ex: Value must replace at Cell(1,1)

Range = Replace(Range, " ", "/", 1, 2)

To:

ActiveSheet.Cells(1, 1).Value = Replace(ActiveSheet.Cells(1, 1).Value, " ", "/", 1, 2)
user_14975970
Related