Splitting Sentences

amitshah

Board Regular
Joined
Apr 13, 2002
Messages
80
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
Howdy,

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

Code:
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
 

Forum statistics

Threads
1,144,766
Messages
5,726,167
Members
422,659
Latest member
RGP268

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top