Splitting Sentences

[amitshah]

Hi,

I needed help with something I am working on in excel. I have a column of text. The length of the text is greater than 30 for eg 45. The length varies upto 60 characters in length. What I need to do is to break it up into two where each new text length should be less than 30 chars. The difficult part comes in when I split the text into two I need to spilt it such that a word is not split halfway for eg.

Orginal Text : “This is a line of text which is greater than 30 charcters”
New Text 1 : “This is a line of text which “
New Text 2: “is greater than 30 charcters”

It should not spilt any word ie it cant be : “This is a line of text whi” it needs “which” to be a whole word, if by chance length is 30 chacrters in the middle of the word the whole word is carried over to the next line of text.

Can someone pls help on how to get this done in a smart way cause I need a list of couple of thousands of rows to do this for and I cant be manually cutting and pasting it. Thank you in advance.

 

[Andrew Poulsom]

See:

http://216.92.17.166/board/viewtopic.php?topic=21881&forum=2

 

[Asala42]

Howdy,

Here's a vba sol'n that may help (it assumes only one split in the sentence with your 30 character limit).

Sub splitsentence()
    'find the last space before 30
    
    StartRow = 2
    EndRow = Range("A65536").End(xlUp).Row
    
    For i = StartRow To EndRow
        
        For j = 30 To 1 Step -1
            If Mid(Cells(i, 1).Text, j, 1) = " " Then
                Cells(i, 2).Value = Left(Cells(i, 1).Text, j)
                Cells(i, 3).Value = Right(Cells(i, 1).Text, Len(Cells(i, 1).Text) - Len(Cells(i, 2).Text))
                j = 1
            End If
        Next j
    Next i
End Sub

HTH
Adam