How can I pinpoint a character in a string and replace it-CodePudding

enter image description here

I am attempting to write a Marco to select the highlighted cell in the image attached (B11), locate the highlighted colon (20th character from the left) and replace it with a dot and loop this to do all 2500 rows in "B"

I am new to writing macros an would appreciate any help you can give me

CodePudding user response：

Small showcase of how would you search for the last occurrence of ":" in Range("B11") and replace it with a "."

Sub replaceTest()
    Dim val As String
    Dim pos As Long
    Dim rng As Range
    
    Set rng = Range("B11")
    
    val = rng.Value
    pos = InStrRev(val, ":")
    
    If pos > 0 Then
        Mid$(val, pos, 1) = "."
        rng.Value = val
    End If
     
    Set rng = Nothing
End Sub

CodePudding user response：

Since your date times 18/10/2022 11:42:10:358 look to be text. I recommend to convert them into a numeric date time (so you can calculate with it and use comparisons which is greater or smaller).

Therefore you need to split the text up into its parts and turn it into a real numeric date using DateSerial and TimeSerial. Finally you need to calculate the milliseconds and add them.

Then you can use .NumberFormat = "DD\/MM\/YYYY hh:mm:ss.000" to format it as you like.

Public Function StringToDateTime(ByVal InputString As String) As Double
    Dim InputDateTime() As String   ' Split into DD/MM/YYYY and hh:mm:ss:000
    InputDateTime = Split(InputString, " ")
    
    Dim InputDate() As String   ' Split into DD and MM and YYYY
    InputDate = Split(InputDateTime(0), "/")
    
    Dim InputTime() As String   ' Split into hh and mm and ss and 000
    InputTime = Split(InputDateTime(1), ":")
        
    Dim RetVal As Double
    RetVal = DateSerial(InputDate(2), InputDate(1), InputDate(0))   TimeSerial(InputTime(0), InputTime(1), InputTime(2))   InputTime(3) / 24 / 60 / 60 / 1000
    
    StringToDateTime = RetVal
End Function

Public Sub Example()
    Dim Cell As Range
    For Each Cell In Range("A1:A5")
        Cell.Value2 = StringToDateTime(Cell.Value2)
        Cell.NumberFormat = "DD\/MM\/YYYY hh:mm:ss.000"
    Next Cell
End Sub