Sub ReplaceColon()

Dim Rng As Range
LastRow = Range("C" & Rows.Count).End(xlUp).Row
Range("D2").Formula = "=text(C2,""HHMM"")"
Range("D2").Copy _
Destination:=Range("D2:D" & LastRow)

End Sub


"Steved" wrote:

Hello from Steved

Whats my Objective

simply replace Semi colon (":")

example 07:40 to 0740 or 13:05 to 1305

The information is in Column C:C and I've asking it to be put in Column D:D
please

Sub ReplaceColon()
For X = 1 To 1
Dim Rng As Range
Set Rng = Format(Range("C2"), "hhmm").End(xlUp)
Range("C2").Select
For Each cell In Rng
If cell.Value < "" Then
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"
ActiveCell.Offset(1, -1).Select
End If
Next cell
Next X
End Sub

Thankyou