Edit: you know, it always helps to have ALL the information when one is attempting to develop a solution.
Your original question stated "all cells containing time".
It did not state "all cells in which a time appears at the end of the cell contents".
That would have made it incredibly simple to come up with a macro to do that, assuming that each cell contains no more than one space. (This assumption may also be a bit of a stretch...)
Sub Redo()
Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If InStr(Cells(i, "A"), ":") Then
Cells(i, "G").Value = Right(Cells(i, "A"), _
(Len(Cells(i, "A")) - Application.Find(" ", Cells(i, "A"))))
Cells(i, "A").Value = ""
End If
Next
End Sub
===========================
There are several points to consider here:
You want to 'move' the cells containing a time value, not copy to another column.
There is no built in 'ISTIME' function in Excel.
You cannot reference a ':' in a cell formatted as Time. The value in the cell is a number between 0 and 1, displayed in a 'time' format.
This can be demonstrated by entering a time in cell A1, then entering this formula in B1:
=Find(".",A1)
and this formula in C1:
=Find(":",A1)
B1 will return '2', as the actual value in A1 is a number such as 0.31, 0.50, etc.
C1 will return a #Value! error as no ':' was found in the string.
The underlying value of a 'time' entry is a number between 0 and 1, with 0 equating to 12:00:00 AM and 1 equating to 11:59:59 PM.
7:31AM, for example, formatted as General would return '0.313194444'. Formatted as Number, to two places, would return '0.31'.
So, if you have other cells in the column that contain values between 0 and 1 that are NOT time values, any macro must be able to differentiate between the cell formats.
Here is a sort of 'brute force' approach that will check all values in column A and if they are formatted as 'Time' will move them to column G, formatting each row in column G as the cell in column A is formatted. Change the "A" column references to the column you wish to validate. Change the "G" references to the column letter you wish to 'relocate' the values to.
Sub tTime()
Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If Cells(i, "A").NumberFormat = "[$-409]h:mm AM/PM;@" Or _
Cells(i, "A").NumberFormat = "[$-409]h:mm:ss AM/PM;@" Or _
Cells(i, "A").NumberFormat = "mm:ss.0;@" Or _
Cells(i, "A").NumberFormat = "[h]:mm:ss;@" Or _
Cells(i, "A").NumberFormat = "[$-F400]h:mm:ss AM/PM" Or _
Cells(i, "A").NumberFormat = "h:mm AM/PM" Or _
Cells(i, "A").NumberFormat = "h:mm:ss AM/PM" Or _
Cells(i, "A").NumberFormat = "h:mm" Or _
Cells(i, "A").NumberFormat = "h:mm:ss" Or _
Cells(i, "A").NumberFormat = "h:mm:ss;@" Then
Cells(i, "G").NumberFormat = Cells(i, "A").NumberFormat
Cells(i, "G").Value = Cells(i, "A").Value
Cells(i, "A").ClearContents
End If
Next
End Sub
Note: if you just enter a 'time' value in a cell, with no prior formatting, the default format assigned is 'Custom - "h:mm".
So, you could possibly eliminate all other 'numberformat' checks in the above macro, keeping only "h:mm".
Now, if you are not concerned about other numerical values in the column that fall between 0 and 1, you could use this macro:
Sub tTime2()
Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If Cells(i, "A").Value >= 0 And Cells(i, "A").Value <= 1 Then
Cells(i, "G").NumberFormat = Cells(i, "A").NumberFormat
Cells(i, "G").Value = Cells(i, "A").Value
Cells(i, "A").ClearContents
End If
Next
End Sub