如何在 Excel 单元格文本中的文本模式 #:## 前插入 CR

Question

我在多行 Excel 电子表格的单元格中有大量文本。单元格中的文本块按时间戳分隔，例如“16:43”或在某些情况下“4:43 PM”。我想解析时间戳并在CHR(10)时间戳前插入一个。

前：

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

后：

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

这个小UDF（）向后扫描输入字符串，寻找冒号。

当它发现冒号时，它会插入一个智利人权委员会(10)在冒号前的适当位置：

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

它可以处理一位和两位数的小时数。
确保输出单元格采用换行格式在.
另一种方法是使用正则表达式。