CODEWITHSUNDEEP
excelvba
Jay
Jay

Reputation: 65

Remove part of the text in the cell and keep the other text

`4/19/2020 6:00:00 PM - this is what the cell has inside of it. I would like to remove the "6:00:00 PM" part but the only function I can find is the .REPLACE function which just eraces the whole cell and turns it blank. There is a different date for each cell so I can't just replace with it the same date every time. 

With Columns("E:E")
.Replace What:="?*:00:00 PM=", Replacement:="?*:00:00 PM "
.Replace What:="?*:00:00 PM", Replacement:=""
End With
With Columns("H:H")
.Replace What:="?*:00:00 PM=", Replacement:="?*:00:00 PM "
.Replace What:="?*:00:00 PM", Replacement:=""
End With

Upvotes: 4

Views: 277

Answers (2)

Gary's Student
Gary's Student

Reputation: 96791

If the values in column E are genuine date/times, then run:

Sub dropTime()
    With Range("E:E").Cells
        .NumberFormat = "m/d/yyyy "
    End With
End Sub

If the values in column E are Text values, then run:

Sub dropTime2()
    Dim rng As Range, r As Range, arr
    Set rng = Intersect(Range("E:E"), ActiveSheet.UsedRange)
    For Each r In rng
        arr = Split(r.Text, " ")
        If UBound(arr) = 2 Then
            r.Value = arr(0)
            r.NumberFormat = "m/d/yyyy "
        End If
    Next r
End Sub

Upvotes: 7

Mech
Mech

Reputation: 4015

Use the format() function.

This answer will find the last row in column 4 ("D") and loop through from row 1 until lRow (last row), formatted every cell as defined.

Sub test()
    Dim rng As Range, cell As Range
    Dim lRow As Long
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets("Data")

    lRow = ws.Cells(ws.Rows.Count, 4).End(xlUp).Row
    Set rng = Range(Cells(1, 4), Cells(lRow, 4))

    For Each cell In rng
        cell = Format(cell, "dd/mm/yyyy")
    Next cell
End Sub

Upvotes: 2

Related Questions