How to insert CR before text pattern #:## in Excel cell text

Question

I have large amounts of text in cells of a multi-row Excel spreadsheet. The text blocks in the cells are broken up by time stamps, e.g., “16:43” or, in some cases “4:43 PM”. I'd like to parse for the time stamps and insert a CHR(10) before the time stamps.

Before:

3:33 AM Waiting for customer permission to remote control desktop.3:33 AM Remote Control successfully initiated.3:35 AM Remote Control ended.3:36 AM Remote Control successfully initiated.3:40 AM The connection to the customer was lost. This session will reconnect if and when the customer reestablishes a network connection.3:40 AM Disconnected (Applet)3:40 AM Remote Control ended.3:45 AM Connecting to: control.app02-01.l

After:

3:33 AM Waiting for customer permission to remote control desktop.
3:33 AM Remote Control successfully initiated.
3:35 AM Remote Control ended.3:36 AM Remote Control successfully initiated.
3:40 AM The connection to the customer was lost. This session will reconnect if and when the customer reestablishes a network connection.
3:40 AM Disconnected (Applet)
3:40 AM Remote Control ended.3:45 AM Connecting to: control.app02-01.l

Answer 1

This small UDF() scans an input string backwards, looking for a colon.

When it finds a colon, it inserts a CHR(10) in the appropriate spot before the colon:

Public Function Fragment(sIN As String) As String
    Dim L As Long, i As Long
    Dim temp As String, CH As String
    L = Len(sIN)
    temp = ""
    For i = L To 1 Step -1
        CH = Mid(sIN, i, 1)
        If CH <> ":" Then
            temp = CH & temp
        Else
            temp = CH & temp
            i = i - 1
            temp = Mid(sIN, i, 1) & temp
            i = i - 1
            CH = Mid(sIN, i, 1)
            If IsNumeric(CH) Then
                temp = Chr(10) & CH & temp
            Else
                temp = CH & Chr(10) & temp
            End If
        End If
    Next i
    Fragment = temp
End Function

It can handle both one and two digit hours.
Make sure the output cell is formatted with wrap on.
Another approach is to use Regex.