How do I turn a column of data into times... via macros

[Hyflex]

How is the best way to change my data into a time, I was thinking of making a macro to put a semi-colon after two numbers & then to format the column as: h:mm but I'm sure there must be a faster and better way to do it. If I could insert a semi-colon in the middle (after two numbers) I would be able to do the rest.

Please help

Raw Data:

0500
0530
1205
0240
0315
0345
0420

The end result I want is:

5:00
5:30
12:05
2:40
3:15
3:45
4:20

 

[Andrew Poulsom]

Here's a formula you can use:

=REPLACE(A1,3,0,":")+0

 

[Comfy]

If your Raw data is formatted as Text

=VALUE(LEFT(A1,2)&":"&RIGHT(A1,2))

Format as time hh:mm

 

[Hyflex]

If your Raw data is formatted as Text

=VALUE(LEFT(A1,2)&":"&RIGHT(A1,2))

Format as time hh:mm

It comes out all weird if I do that and puts a date before it too I have no idea why

 

[Andrew Poulsom]

Did you try my formula?

 

[Hyflex]

Did you try my formula?

I can't use a formula because currently that's.... over 2000 rows, that would be really inefficient

 

[Arrose]

2000 rows is peanuts.

 

[Comfy]

I can't use a formula because currently that's.... over 2000 rows, that would be really inefficient

But you tried mine?

 

[Hyflex]

But you tried mine?

I tried both formulas yours and his :/

2000 rows is peanuts.

2000 rows in one of my documents, 6000 in another, 2500 in another and there are another 5 documents after that to do...

 

[Andrew Poulsom]

A macro:

Sub Test()
    Dim Cell As Range
    With ActiveSheet
        For Each Cell In .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
            Cell.NumberFormat = "hh:mm"
            Cell.Value = TimeValue(WorksheetFunction.Replace(Cell.Value, 3, 0, ":"))
        Next Cell
    End With
End Sub