How to remove characters from the right of a space for all cells in specific columns

Question

Sorry if this is a stupid question, but I am new to vba and I am trying to remove characters from the right of a space

an example of the text in the cell is - 5/08/2015 at 12:22 PM

I want to reduce it to 5/08/2015 (d/mm/yyyy)

cell is formatted as Date (d/mm/yyyy)

I have come up with:

Dim tmp As String
Dim DateOnly As String
tmp = Range("E3").Value
DateOnly = Left(tmp, InStr(tmp, " ") - 1)
Range("E3").Value = DateOnly

this removes the time from the end of the cell, but it converts the date to 8/05/2015

I am also struggling to get it to work on all cells within 3 columns ("E:G")

Can anyone assist please?

Answer 1

To solve the first problem, try this modification:

Range("E3").Value = CDate(DateOnly)

Answer 2

To handle all three columns (starting on Row 3 downward)...

Range("E3:G" & Rows.Count).Replace " *", "", xlPart, , , False, False