Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have notes stored in an Excel cell. I add line breaks and dates every time I add a new note.

I need to copy this to another program, but it has a line limit of 50 characters. I want a line break for each new date and for when each date's comment goes over 50 characters.

I'm able to do one or the other, but I can't figure out how to do both. I'd prefer words not to be split up, but at this point I don't care.

Below is some sample input. If needed for a =SUBSTITUTE or =REPLACE function, I could add a ~ before each date in my input as a delimiter.

Sample Input:

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 customeremail@customer.com  
05/14 - Copy sent to John Public and email@customer.com  

Ideal Output:

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 customeremail@customer.c  
om  
05/14 - Copy sent to John Public and email@custome  
r.com  
share|improve this question

1 Answer 1

up vote 0 down vote accepted

Here's something you can use to put 50 char increments into a collection object with which you can either write out to another worksheet or write out to a csv or whatever. Jsut iterate over it and do what you want with the contents

Function to get 50 or less characters worth of words

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

Function to Move through your whole range of cells. Call this function and it will return the collection of ~50 char lines

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

Edit:

You can make use of this with the following few lines. The FileSystemObject requires you add a reference to the 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.