when breaking a sentence how to check whether it does not break word?

[ndp]

Hi,
I am writing a macro in excel. I am looking through each row in a column for length and if it exceeds 50 characters then I move extra text to the line below. My problem is it breaks the ending word some times.
For example if the sentence is..
"Check the length of the sentence to see if it is more than 50 characters."
The sentence is broken like this...
Fisrt line: "Check the length of the sentence to see if it is m"
Second line: "ore than 50 characters."

Can anybody suggest a way so that I get second line like this "more than 50 characters." and remove m from the first line?

Thanks in advance,
ndp

 

[Nelviticus]

Here's a dummy function you can tweak:

Sub boo()
    
    Dim sText As String     ' The text we want to truncate
    Dim sPart1 As String    ' First part of the chopped string
    Dim sPart2 As String    ' Second part of the chopped string
    Dim iChop As Integer    ' The point we ideally want to chop it at
    Dim iPos As Integer     ' Current position in string
    
    iChop = 50
    
    ' Grab the text we want to look at
    sText = Cells(1, 1).Text
    
    ' If it's too long
    If Len(sText) > iChop Then
        
        iPos = iChop
        
        ' Move back through the string until we
        ' find a space or reach the start
        Do While Mid(sText, iPos, 1) <> " " And iPos > 0
            
            iPos = iPos - 1
            
        Loop
        
        ' If we didn't reach the start, iPos will be the
        ' position of the first space BEFORE our chop point
        If iPos > 0 Then
            
            ' Split the string in two
            sPart1 = Trim(Left(sText, iPos))
            sPart2 = Trim(Right(sText, Len(sText) - iPos))
            
        End If
        
    End If
    
End Sub

Nelviticus

 

[ndp]

Thanks so much Nelviticus.

I did it the way you suggested and it works. I was also trying to use InstrRev function which gives position of the character from the end, but I have Excel 97 and it doesn't work with that.

Thanks again and really appreciate your help.
ndp

 

[Nelviticus]

Happy to help

Nelviticus