I am designing a 'program' in excel to help capture information on forms that are received by my employer. the idea is that the user fills in the blanks, and the pre-defined text is completed with the added data using the concatenate function. However, this information is then to be copied from excel and pasted into a program that is specific to our job. This program only allows 72 characters per line of text, and DOES NOT wrap text across lines.

So, if you are 5 spaces from the end of the line, and wish to type 'sentence' you end up with 'sente' on the first line and 'nce' on the second.

I am looking for a way for count back from the end of the line (if you are still typing at that point, i.e. last character of line is not a space) to the last 'space' that was entered, then force a carriage return, and start the process again. This has to be able to handle up to 1000 characters.

If you know of a way to do this, I would be ecstatic if you could tell me how it is done. Oh yeah, the text is all kept in a certain cell (L3 at the moment).

Thank you.

I don't think it's possible to evaluate -on the fly- in Excel and I can't find any smart solutions. If someone else can, I'd like to know too.

This sub must be run when all text has been entered into the cell. I don't know whether the receiving program will be able to understand the linebreak made with chr(10). If not, try substituting chr(10) with VBCRLF in the sub.

Sub BreakSentence()
Dim Sentence, NewSentence As String
Dim Lngt, Pos, o, p As Long

Sentence = Cells(3, 12).Value
Lngt = Len(Sentence)

' more than 1 line ?
    If Lngt < 72 Then
        Exit Sub
    End If

    Do Until Lngt < 73

        ' if we're not lucky that pos 73 is empty then...
        If Not Mid(Sentence, 73, 1) = " " Then

        ' ... go backwards to the first empty space and save the position in Pos
        ' and exit the loop
            For p = 72 To 1 Step -1
                If Mid(Sentence, p, 1) = " " Then
                    Pos = p
                    Exit For
                End If
            Next p
        ' Add characters from pos1 to Pos of original sentence, in NewSentence and...
                If NewSentence = "" Then
                    NewSentence = Left(Sentence, Pos)
                Else
                    NewSentence = NewSentence & Chr(10) & Left(Sentence, Pos)
                End If
        ' ...make Sentence contain the rest of the original sentence
                Sentence = Right(Sentence, Lngt - Pos)
        Else
        ' Add characters from pos1 to 72 of original sentence, in NewSentence and...
                If NewSentence = "" Then
                    NewSentence = Left(Sentence, 72)
                Else
                    NewSentence = NewSentence & Chr(10) & Left(Sentence, 72)
                End If
        ' ...make Sentence contain the rest of the original sentence
                Sentence = Right(Sentence, Lngt - 72)
        End If

        ' redefine the length of the sentence
        Lngt = Len(Sentence)
    Loop

' Add the remains of the sentence to NewSentence
NewSentence = NewSentence & Chr(10) & Sentence
Cells(3, 12).Value = NewSentence

End Sub