☰

Excel 2007 - 在单元格中添加换行符，且行不超过 50 个字符

tag-icon • tag-icon microsoft-excel microsoft-excel-2007 worksheet-function
Excel 2007 - 在单元格中添加换行符，且行不超过 50 个字符

我在 Excel 单元格中存储了笔记。每次添加新笔记时，我都会添加换行符和日期。

我需要将其复制到另一个程序，但它的行数限制为 50 个字符。我希望每个新日期以及每个日期的评论超过 50 个字符时都换行。

我可以做其中一种，但我不知道如何同时做两种。我更希望单词不要被拆分，但目前我不在乎。

以下是一些示例输入。如果需要=SUBSTITUTE或=REPLACE函数，我可以~在输入中的每个日期前添加一个作为分隔符。

示例输入：

07/03 - FU on query. Copies and history included. CC to Jane Doe and John Public  
06/29 - Cust claiming not to have these and wrong PO on query form. Responded with inv  sent dates and locations, correct PO values, and copies.  
06/27 - New ticket opened using query form  
06/12 - Opened ticket with helpdesk asking status  
05/21 - Copy submitted to [email protected]  
05/14 - Copy sent to John Public and [email protected]

理想输出：

07/03 - FU on query. Copies and history included.  
CC to Jane Doe and John Public  
06/29 - Cust claiming not to have these and wrong  
PO on query form. Responded with inv sent dates an  
d locations, correct PO values, and copies.  
06/27 - New ticket opened using query form  
06/12 - Opened ticket with helpdesk asking status  
05/21 - Copy submitted to [email protected]  
om  
05/14 - Copy sent to John Public and email@custome  
r.com

答案1

你可以用这个东西将 50 个字符的增量放入一个集合对象中，然后将其写入另一个工作表或写入 csv 或任何其他文件。只需对其进行迭代，然后对内容执行所需的操作即可

获取 50 个或更少字符的单词的函数

Private Function FindFirst50ishChars(contents As String) As String
    Dim charSum As Integer, splitContents() As String, j As Integer
            Dim returnString As String: returnString = ""
        splitContents = Split(contents, " ")
        charSum = 0
            If Len(contents) <= 50 Then
                returnString = contents
            Else
                For j = LBound(splitContents) To UBound(splitContents)

                    If charSum + Len(splitContents(j)) >= 50 Then
                        Exit For
                    Else
                        returnString = returnString & " " & splitContents(j)
                        charSum = charSum + Len(splitContents(j)) + 1 '+1 for the extra space added
                        Debug.Print Len(returnString)
                    End If
                Next j
            End If
        FindFirst50ishChars = Trim(returnString)
End Function

函数用于移动整个单元格范围。调用此函数，它将返回约 50 个字符行的集合

Function GetLinesIn50CharIncrements(StartRow As Integer, EndRow As Integer, Column As Integer) As Collection

    Dim row As Integer, j As Integer
    Dim aWs As Worksheet, contents As String
    Dim WholeLineConsumed As Boolean
    Set aWs = ActiveSheet
    Dim linesCollection As Collection: Set linesCollection = New Collection

    For row = StartRow To EndRow
        contents = aWs.Cells(row, Column)
        WholeLineConsumed = False
        Do While Not WholeLineConsumed
            Dim first50 As String
            first50 = FindFirst50ishChars(contents)
            linesCollection.Add first50
            contents = Right(contents, Len(Trim(contents)) - Len(first50))
            If contents = "" Then WholeLineConsumed = True
        Loop
    Next row
    Set GetLinesIn50CharIncrements = linesCollection
End Function

编辑：

您可以使用以下几行来使用它。FileSystemObject需要您添加对 Microsoft Scripting Runtime 的引用

Dim fso As FileSystemObject: Set fso = New FileSystemObject
Dim FiftyCharLines As Collection: Set FiftyCharLines = GetLinesIn50CharIncrements(1, 6, 1)
Dim i As Integer, f As TextStream
Dim fileName As String: 'fileName = "some fully qualified file path"
Set f = fso.OpenTextFile(fileName, ForWriting, True)


For i = 1 To FiftyCharLines.Count
    f.WriteLine FiftyCharLines(i)
Next i
f.Close

相关内容