Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Split Sentence Into 40 Character Whole Words

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Split Sentence Into 40 Character Whole Words

    I have created a macro to split a column to 40 characters. The split moves to next rows but I want split to move to columns. i.e. If cell A1 is 100 characters long. The split should be B1, C1 and so on. Any help appreciated.

  2. #2
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    2,391
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Split Sentence Into 40 Character Whole Words

    Can you post your code?
    I visit this site mainly to remember how little I know

  3. #3
    New Member
    Join Date
    Mar 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split Sentence Into 40 Character Whole Words

    Code:
    Sub test()
        Dim oneCell As Range
        Dim Lines As Variant
        Dim i As Long
        Dim LengthOfLine As Long
        
        LengthOfLine = 40
        With Sheet1.Range("A:A")
            For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 1 Step -1
                Set oneCell = .Cells(i, 1)
                With oneCell
                    Lines = LInesOfLength(LengthOfLine, oneCell.Text)
                    If 1 < UBound(Lines) Then
                        .Offset(1, 0).Resize(UBound(Lines) - 1, 1).EntireRow.Insert shift:=xlDown
                    End If
                    .Offset(0, 1).Resize(UBound(Lines), 1).Value = Application.Transpose(Lines)
                End With
            Next i
        End With
    End Sub
    
    
    Function LInesOfLength(ByVal LineLength As Long, ByVal aString As String) As Variant
        Dim Result() As String
        Dim FirstLine As String
        Dim LinePointer As Long
        
        aString = Trim(aString)
        If aString = vbNullString Then
            ReDim Result(1 To 1)
        Else
            ReDim Result(1 To Len(aString))
            Do
                FirstLine = Left(aString, LineLength)
                If InStr(1, FirstLine, " ") = 0 Then
                    FirstLine = Split(aString, " ")(0)
                Else
                    If Mid(aString, LineLength + 1, 1) = " " Or Mid(aString, LineLength + 1, 1) = vbNullString Then
                        Rem done
                    Else
                        FirstLine = Left(FirstLine, InStrRev(FirstLine, " ") - 1)
                    End If
                End If
                
                LinePointer = LinePointer + 1
                Result(LinePointer) = FirstLine
                aString = Trim(Replace(aString, FirstLine, vbNullString, 1, 1))
            Loop Until aString = vbNullString
            
            ReDim Preserve Result(1 To LinePointer)
            
        End If
        LInesOfLength = Result
    End Function
    Last edited by Fluff; Mar 16th, 2018 at 02:04 PM. Reason: Code tags

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    13,496
    Post Thanks / Like
    Mentioned
    249 Post(s)
    Tagged
    17 Thread(s)

    Default Re: Split Sentence Into 40 Character Whole Words

    Hi & welcome to the board.
    Try
    Code:
    Sub test()
        Dim oneCell As Range
        Dim Lines As Variant
        Dim i As Long
        Dim LengthOfLine As Long
        
        LengthOfLine = 20
        With Sheet1.Range("A:A")
            For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 1 Step -1
                Set oneCell = .Cells(i, 1)
                With oneCell
                    Lines = LInesOfLength(LengthOfLine, oneCell.Text)
                    .Offset(0, 1).Resize(, UBound(Lines)).Value = Lines
                End With
            Next i
        End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,098
    Post Thanks / Like
    Mentioned
    58 Post(s)
    Tagged
    20 Thread(s)

    Default Re: Split Sentence Into 40 Character Whole Words

    Here is a fully self-contained macro (no side function calls required) that you can consider...
    Code:
    Sub SplitTextOnSpacesWithMaxCharactersPerLine() Dim Text As String, TextMax As String, SplitText As String, Answer() As String Dim Space As Long, Source As Range, CellWithText As Range Const MaxChars As Long = 40 Const DestinationOffset As Long = 1 Set Source = Range("A1", Cells(Rows.Count, "A").End(xlUp)) On Error GoTo 0 For Each CellWithText In Source Text = CellWithText.Value SplitText = "" Do While Len(Text) > MaxChars TextMax = Left(Text, MaxChars + 1) If Right(TextMax, 1) = " " Then SplitText = SplitText & RTrim(TextMax) & vbLf Text = Mid(Text, MaxChars + 2) Else Space = InStrRev(TextMax, " ") If Space = 0 Then SplitText = SplitText & Left(Text, MaxChars) & vbLf Text = Mid(Text, MaxChars + 1) Else SplitText = SplitText & Left(TextMax, Space - 1) & vbLf Text = Mid(Text, Space + 1) End If End If Loop Answer = Split(SplitText & Text, vbLf) CellWithText.Offset(, DestinationOffset).Resize(, UBound(Answer) + 1).Value = Answer Next Exit Sub End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    2,391
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Split Sentence Into 40 Character Whole Words

    Rick, SplitText = "" I've seen this a few times when people are working with string variables, is it setting the variable to some sort of array?
    I visit this site mainly to remember how little I know

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,098
    Post Thanks / Like
    Mentioned
    58 Post(s)
    Tagged
    20 Thread(s)

    Default Re: Split Sentence Into 40 Character Whole Words

    Quote Originally Posted by Dryver14 View Post
    Rick, SplitText = "" I've seen this a few times when people are working with string variables, is it setting the variable to some sort of array?
    No, SplitText is Dim'med as a String variable, so it cannot be an array. What that line of code is doing is setting the SplitText variable to the empty text string ("") so that there is nothing in it (left over from the previous loop) as a new loop starts. Think of it a "clearing" a String variable.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    2,391
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Split Sentence Into 40 Character Whole Words

    Thanks, good explanation.
    I visit this site mainly to remember how little I know

  9. #9
    New Member
    Join Date
    Mar 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split Sentence Into 40 Character Whole Words

    Quote Originally Posted by Fluff View Post
    Hi & welcome to the board.
    Try
    Code:
    Sub test()
        Dim oneCell As Range
        Dim Lines As Variant
        Dim i As Long
        Dim LengthOfLine As Long
        
        LengthOfLine = 20
        With Sheet1.Range("A:A")
            For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 1 Step -1
                Set oneCell = .Cells(i, 1)
                With oneCell
                    Lines = LInesOfLength(LengthOfLine, oneCell.Text)
                    .Offset(0, 1).Resize(, UBound(Lines)).Value = Lines
                End With
            Next i
        End With
    End Sub
    I have highlighted in red where I get error.

  10. #10
    New Member
    Join Date
    Mar 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split Sentence Into 40 Character Whole Words

    Perfect!!! Thanks for the help

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •