macro to change text to time
 

I have a large spreadsheet of information extracted as text. The time
columns are entered as military time, but do not have the colons, 735, 1257.
Would anyone know a macro that can be run down the columns to change the
number to time, 07:35 AM, 12:57 PM? Thanks,

A regular formula would be...
=VALUE(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))

A macro for a selected range of text would be...
Sub Macro1()
Dim rngCell As Range
For Each rngCell In Selection
rngCell.Value = _
Left(rngCell.Value, Len(rngCell.Value) - 2) _
& ":" & Right(rngCell.Value, 2)
Next rngCell
End Sub

HTH,
--
Gary Brown

If this post was helpful, please click the ''''''''Yes'''''''' button next
to ''''''''''''''''Was this Post Helpfull to you?".


"JRV" wrote:

I have a large spreadsheet of information extracted as text. The time
columns are entered as military time, but do not have the colons, 735, 1257.
Would anyone know a macro that can be run down the columns to change the
number to time, 07:35 AM, 12:57 PM? Thanks,