Split a Column into 2, use a character limit that does not cut words in half.

[vishesh10667]

Title says it all.

I have huge columns of data that can only be 30 characters long. I want to use a formula to split the column into two. The original column will have a character limit of 30, and should not cut words in half. If the Length will be more than 30 while including the entire last word of the cell, that word should be the first displayed in adjacent cell in the 2nd column.

The 2nd column should also be 30 characters long. And rest in 3 column


I usually use a Text to Columns method, but dont know how to make it not cut off the words. Any ideas?

Thanks in advance

 

[vishesh10667]

Hmm, you must be using a very old Excel version. You really should state that in any questions you ask as helpers will generally expect something newer than that.

Yes it is 2007 version

 

[Peter_SSs]

Yes it is 2007 version

Excel 2007 allows up to 64 levels of nesting so the version of the program should not be the problem for the nesting levels with that formula, but the file type version may be.
Is your file type *.xls ?
If so, save it as *.xlsx or *.xlsm format and the nesting level problem should be resolved

BTW, why not include your Office version in your profile, not just your Platform?

 

[V620898]

Use below formula next to input colomn
=LEFT(LEFT(A1,50),FIND(CHAR(1),SUBSTITUTE(LEFT(A1,50)," ",CHAR(1),LEN(LEFT(A1,50))-LEN(SUBSTITUTE(LEFT(A1,50)," ",""))-1))-1)

In the next of this

=LEFT(MID(A1,LEN(B1)+2,51),FIND(CHAR(1),SUBSTITUTE(MID(A1,LEN(B1)+2,51)," ",CHAR(1),LEN(MID(A1,LEN(B1)+2,51))-LEN(SUBSTITUTE(mswip1," ",""))-2))-1)

And in the last colomn

=MID(A1,LEN(B1)+LEN(C1)+3,100)

This does not require macro

 

[zvcknvrv]

Welcome to the MrExcel board!

Try these formulas copied down.

Book1
	A	B	C	D
1	This cell contains enough characters to exceed the column limit	This cell contains enough	characters to exceed the	column limit
2	Short text	Short text
3	This should have a break after 30 characters	This should have a break after	30 characters
Split column

Cell Formulas
Range	Formula
B1:B3	B1	=IF(LEN(A1)<=30,A1,LEFT(A1,FIND("#",SUBSTITUTE(LEFT(A1,31)," ","#",LEN(LEFT(A1,31))-LEN(SUBSTITUTE(LEFT(A1,31)," ",""))))-1))
C1:C3	C1	=IF(LEN(A1)<=61,REPLACE(A1,1,LEN(B1)+1,""),LEFT(REPLACE(A1,1,LEN(B1)+1,""),FIND("#",SUBSTITUTE(LEFT(REPLACE(A1,1,LEN(B1)+1,""),31)," ","#",LEN(LEFT(REPLACE(A1,1,LEN(B1)+1,""),31))-LEN(SUBSTITUTE(LEFT(REPLACE(A1,1,LEN(B1)+1,""),31)," ",""))))-1))
D1:D3	D1	=TRIM(SUBSTITUTE(A1,TRIM(B1&" "&C1),""))

If you wanted a macro to break up the column A values then you could try this in a copy of your workbook.

Sub BreakItUp()
  Dim s As String
  Dim k As Long
  Dim result As Variant
  Dim c As Range

  Const CharsPerLine As Long = 30     '<-Change to suit

  For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
    s = c.Text
    If Len(s) > 0 Then
      k = 0
      ReDim result(1 To Len(s) / CharsPerLine + 1)
      Do Until Len(s) = 0
        k = k + 1
        result(k) = RTrim(Left(s, InStrRev(s & Space(CharsPerLine), " ", CharsPerLine + 1) - 1))
        s = Mid(s, Len(result(k)) + 2)
      Loop
      c.Offset(, 1).Resize(, k).Value = result
    End If
  Next c
End Sub

Hey there, thank you so much for the code i really liked it, and it does the job perfectly. I do split random phrases with Excel 2019 and the script worked until it comes to a long phrase and I got this error "Runtime error '9' : Subscript out of range. could you please help me if something should be added to the code to take care of long phrases.

 

[zvcknvrv]

Hey there, thank you so much for the code i really liked it, and it does the job perfectly. I do split random phrases with Excel 2019 and the script worked until it comes to a long phrase and I got this error "Runtime error '9' : Subscript out of range. could you please help me if something should be added to the code to take care of long phrases.

Thank you again, I did find the solution you posted it before.

 

[Peter_SSs]

I did find the solution you posted it before.

Cheers. Glad you got it sorted. Thanks for letting us know.

BTW, Welcome to the MrExcel board!

 

[Amit Tandon]

P

previously it was mentioned as 2 columns then i made the corrections to 3 columns

Refer the enclosed image (image has been attached to illustrate the actual formula and results):

Enter below formula in cell B2:
=IF(OR(MID(A2,31,1)=" ",LEN(A2)<=30),LEFT(A2,30),LEFT(A2,FIND("|",SUBSTITUTE(LEFT(A2,30)," ","|",LEN(LEFT(A2,30))-LEN(SUBSTITUTE(LEFT(A2,30)," ",""))))-1))

Enter below formula in cell C2:
=IF(OR(MID(MID(A2,LEN(B2)+2,LEN(A2)),31,1)=" ",LEN(MID(A2,LEN(B2)+2,LEN(A2)))<=30),LEFT(MID(A2,LEN(B2)+2,LEN(A2)),30), LEFT(MID(A2,LEN(B2)+2,LEN(A2)), FIND("|",SUBSTITUTE(LEFT(MID(A2,LEN(B2)+2,LEN(A2)),30)," ","|", LEN(LEFT(MID(A2,LEN(B2)+2,LEN(A2)),30))-LEN(SUBSTITUTE(LEFT(MID(A2,LEN(B2)+2, LEN(A2)),30)," ",""))))-1))

Enter below formula in cell D2:
=MID(A2,LEN(B2)+LEN(C2)+3,LEN(A2))

 

[zvcknvrv]

Cheers. Glad you got it sorted. Thanks for letting us know.

BTW, Welcome to the MrExcel board!

Hey How are you doing, I hope everyone is safe, I like the Idea of Vba instead of functions as I told you before I'm working with it thanks to you, but I have a question or maybe 2:
1. Can I give the code 2nd number of characters to split and 3rd number like "1st cell should be 30 characters (as its already in the code) 2nd cell should be only 20 cell and so on ..." if you can help me with that
2nd question is:
2. Can I give the code a specific name and split the phrase from that name Like : a simple 2 paragraphs with 2nd one got a "specific word" before it ?
thank you so much!

 

[Rick Rothstein]

1. Can I give the code 2nd number of characters to split and 3rd number like "1st cell should be 30 characters (as its already in the code) 2nd cell should be only 20 cell and so on ..." if you can help me with that

Here is a UDF that you can use (see description on how to use it below)...

Function WrapText(CellWithText As String, WhichOne As Long, ParamArray MaxChars() As Variant) As String
  Dim Space As Long, Index As Long, Text As String, TextMax As String, Arr() As String
  Text = CellWithText
  Index = LBound(MaxChars)
  Do While Len(Text) > MaxChars(Index)
    TextMax = Left(Text, MaxChars(Index) + 1)
    If Right(TextMax, 1) = " " Then
      WrapText = WrapText & RTrim(TextMax) & Chr(0)
      Text = Mid(Text, MaxChars(Index) + 2)
    Else
      Space = InStrRev(TextMax, " ")
      If Space = 0 Then
        WrapText = WrapText & Left(Text, MaxChars(Index)) & Chr(0)
        Text = Mid(Text, MaxChars(Index) + 1)
      Else
        WrapText = WrapText & Left(TextMax, Space - 1) & Chr(0)
        Text = Mid(Text, Space + 1)
      End If
    End If
    Index = Application.Min(UBound(MaxChars), Index + 1)
  Loop
  Arr = Split(WrapText & Text, Chr(0))
  If WhichOne <= 1 + UBound(Arr) Then WrapText = Arr(WhichOne - 1) Else WrapText = ""
End Function

The first argument for this function is the text you want to split apart which can either be quoted text or a reference to a cell that contains the text to be split apart. The second argument is the number of the split apart text "paragraph" that you want to put in that cell (first "paragraph" is number 1, second "paragraph" is number 2 and so on). If there are more "paragraphs" than numbers provided, the code with use the last number to create all the remaining "paragraphs". To make this numbering automatic as you drag the formula across, you can use COLUMNS($A:A) in the first cell. After the second argument, you would place a list of the "paragraph" lengths you want for each "paragraph". So, let's say your text is in cell C3 and you wanted to split the text so that the first "paragraph" was no more than 30 characters long, the second "paragraph" was no more than 20 characters long and all the remaining "paragraphs" were no more than 25 characters long, then you would put this formula in cell D3 and copy it across...

=WrapText(C3,COLUMNS($A:A),30,20,25)

.

 

[zvcknvrv]

Here is a UDF that you can use (see description on how to use it below)...

Function WrapText(CellWithText As String, WhichOne As Long, ParamArray MaxChars() As Variant) As String
  Dim Space As Long, Index As Long, Text As String, TextMax As String, Arr() As String
  Text = CellWithText
  Index = LBound(MaxChars)
  Do While Len(Text) > MaxChars(Index)
    TextMax = Left(Text, MaxChars(Index) + 1)
    If Right(TextMax, 1) = " " Then
      WrapText = WrapText & RTrim(TextMax) & Chr(0)
      Text = Mid(Text, MaxChars(Index) + 2)
    Else
      Space = InStrRev(TextMax, " ")
      If Space = 0 Then
        WrapText = WrapText & Left(Text, MaxChars(Index)) & Chr(0)
        Text = Mid(Text, MaxChars(Index) + 1)
      Else
        WrapText = WrapText & Left(TextMax, Space - 1) & Chr(0)
        Text = Mid(Text, Space + 1)
      End If
    End If
    Index = Application.Min(UBound(MaxChars), Index + 1)
  Loop
  Arr = Split(WrapText & Text, Chr(0))
  If WhichOne <= 1 + UBound(Arr) Then WrapText = Arr(WhichOne - 1) Else WrapText = ""
End Function

The first argument for this function is the text you want to split apart which can either be quoted text or a reference to a cell that contains the text to be split apart. The second argument is the number of the split apart text "paragraph" that you want to put in that cell (first "paragraph" is number 1, second "paragraph" is number 2 and so on). If there are more "paragraphs" than numbers provided, the code with use the last number to create all the remaining "paragraphs". To make this numbering automatic as you drag the formula across, you can use COLUMNS($A:A) in the first cell. After the second argument, you would place a list of the "paragraph" lengths you want for each "paragraph". So, let's say your text is in cell C3 and you wanted to split the text so that the first "paragraph" was no more than 30 characters long, the second "paragraph" was no more than 20 characters long and all the remaining "paragraphs" were no more than 25 characters long, then you would put this formula in cell D3 and copy it across...

=WrapText(C3,COLUMNS($A:A),30,20,25)

.

Hi, Thank you and I did found some difficulties to understand all this but I guess I didn't explain correctly because the paragraph question is separated from the first like 100% not related, so I will explain more.
1. the code that Peter provides us Split in a fixed number of characters (Ex: 30 characters per cell) what I want is : The first cell should be 30 and 2nd cell should be 20 and 3rd should be 10 characters.
2. for paragraph question Im asking for another code : that search for a specific word and cut the text after it and past it in the next cell (ex: 5 Blog posts with 2 Paragraphs each and have got "HOW TO USE" heading for 2nd paragraph, so I want all 1st paragraphs in a column "including HOW TO USE heading" and 2nd paragraphs in the next columns.