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.
CodePudding user response:
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)
CodePudding user response:
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