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

vishesh10667

New Member
Joined
Nov 26, 2019
Messages
18
Office Version
2007
Platform
Windows
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

KOKOSEK

Board Regular
Joined
Apr 8, 2019
Messages
209
Office Version
365, 2013
Platform
Windows
Am I correct:
original column (x long) --> 1st column (30 long without cutting words) + 2nd (30 long without cutting words) + 3rd column (the rest if needed)

p.s. What for you've posted your post twice?
 

vishesh10667

New Member
Joined
Nov 26, 2019
Messages
18
Office Version
2007
Platform
Windows
Y
Am I correct:
original column (x long) --> 1st column (30 long without cutting words) + 2nd (30 long without cutting words) + 3rd column (the rest if needed)

p.s. What for you've posted your post twice?
Yes Exactly
 

vishesh10667

New Member
Joined
Nov 26, 2019
Messages
18
Office Version
2007
Platform
Windows
P
Am I correct:
original column (x long) --> 1st column (30 long without cutting words) + 2nd (30 long without cutting words) + 3rd column (the rest if needed)

p.s. What for you've posted your post twice?
previously it was mentioned as 2 columns then i made the corrections to 3 columns
 

KOKOSEK

Board Regular
Joined
Apr 8, 2019
Messages
209
Office Version
365, 2013
Platform
Windows
A
B
C
D
2
I have huge columns of data that can only be 30 characters longI have huge columns of datathat can only be 30characters long
3
I want to use a formula to split the column into three.I want to use a formula tosplit the column intothree.
4
The original column will have a character limit of 30, and should not cut words in halfThe original column will havea character limit of 30, andshould not cut words in half
5
If the Length will be more than 30 while including the next 30 characters of the cellIf the Length will be morethan 30 while including thenext 30 characters of the cell
6
that word should be the first displayed in adjacent cell in the 2nd column.that word should be the firstdisplayed in adjacent cell inthe 2nd column.
7
The 2nd column should also be 30 characters long. And rest in 3 columnThe 2nd column should also be30 characters long. And restin 3 column
I've used UDF like below (please paste it into VBA module):

Code:
Function RevSrch(str As String, find As String)
    RevSrch = InStrRev(str, find)
End Function
A
B
C
D
2
I have huge columns of data that can only be 30 characters long=IF(MID(A2,31,1)<>" ",LEFT(A2,RevSrch(LEFT(A2,30)," ")),LEFT(A2,30))=IF(MID(MID(A2,FIND(B2,A2)+LEN(B2),30),30,1)=" ",MID(A2,FIND(B2,A2)+LEN(B2),30),LEFT(MID(A2,FIND(B2,A2)+LEN(B2),30),RevSrch(LEFT(MID(A2,FIND(B2,A2)+LEN(B2),30),30)," ")))=MID(A2,FIND(C2,A2)+LEN(C2),LEN(A2))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,731
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

Try these formulas copied down.

Book1
ABCD
1This cell contains enough characters to exceed the column limit
This cell contains enough
characters to exceed the
column limit
2Short text
Short text
 
 
3This should have a break after 30 characters
This should have a break after
30 characters
 
Split column
Cell Formulas
RangeFormula
B1:B3B1=IF(LEN(A1)<=30,A1,LEFT(A1,FIND("#",SUBSTITUTE(LEFT(A1,31)," ","#",LEN(LEFT(A1,31))-LEN(SUBSTITUTE(LEFT(A1,31)," ",""))))-1))
C1:C3C1=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:D3D1=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.

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

KOKOSEK

Board Regular
Joined
Apr 8, 2019
Messages
209
Office Version
365, 2013
Platform
Windows
Peter_SSs: formula from middle column (C1) cut of first letter from string. Look:



A
B
C
D
E
F
G
1
KOKOSEK's formulas​
Peter_SS's formulas​
2
I have huge columns of data that can only be 30 characters longI have huge columns of datathat can only be 30characters longI have huge columns of datahat can only be 30 characterscharacters long
3
I want to use a formula to split the column into three.I want to use a formula tosplit the column intothree.I want to use a formula toplit the column into three.three.
4
The original column will have a character limit of 30, and should not cut words in halfThe original column will havea character limit of 30, andshould not cut words in halfThe original column will havecharacter limit of 30, andshould not cut words in half
5
If the Length will be more than 30 while including the next 30 characters of the cellIf the Length will be morethan 30 while including thenext 30 characters of the cellIf the Length will be morehan 30 while including thenext 30 characters of the cell
6
that word should be the first displayed in adjacent cell in the 2nd column.that word should be the firstdisplayed in adjacent cell inthe 2nd column.that word should be the firstisplayed in adjacent cell inthe 2nd column.
7
The 2nd column should also be 30 characters long. And rest in 3 columnThe 2nd column should also be30 characters long. And restin 3 columnThe 2nd column should also be0 characters long. And rest inin 3 column
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,731
Office Version
365
Platform
Windows
Peter_SSs: formula from middle column (C1) cut of first letter from string. Look:
Looks okay to me. :)

Book1
ABCD
1This cell contains enough characters to exceed the column limit
This cell contains enough
characters to exceed the
column limit
2Short text
Short text
 
 
3This should have a break after 30 characters
This should have a break after
30 characters
 
4I have huge columns of data that can only be 30 characters long
I have huge columns of data
that can only be 30 characters
long
5I want to use a formula to split the column into three.
I want to use a formula to
split the column into three.
 
6The original column will have a character limit of 30, and should not cut words in half
The original column will have
a character limit of 30, and
should not cut words in half
7If the Length will be more than 30 while including the next 30 characters of the cell
If the Length will be more
than 30 while including the
next 30 characters of the cell
8that word should be the first displayed in adjacent cell in the 2nd column.
that word should be the first
displayed in adjacent cell in
the 2nd column.
9The 2nd column should also be 30 characters long. And rest in 3 column
The 2nd column should also be
30 characters long. And rest
in 3 column
Split column
Cell Formulas
RangeFormula
B1:B9B1=IF(LEN(A1)<=30,A1,LEFT(A1,FIND("#",SUBSTITUTE(LEFT(A1,31)," ","#",LEN(LEFT(A1,31))-LEN(SUBSTITUTE(LEFT(A1,31)," ",""))))-1))
C1:C9C1=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:D9D1=TRIM(SUBSTITUTE(A1,TRIM(B1&" "&C1),""))
 

vishesh10667

New Member
Joined
Nov 26, 2019
Messages
18
Office Version
2007
Platform
Windows
Welcome to the MrExcel board!

Try these formulas copied down.

Book1
ABCD
1This cell contains enough characters to exceed the column limit
This cell contains enough
characters to exceed the
column limit
2Short text
Short text
 
 
3This should have a break after 30 characters
This should have a break after
30 characters
 
Split column
Cell Formulas
RangeFormula
B1:B3B1=IF(LEN(A1)<=30,A1,LEFT(A1,FIND("#",SUBSTITUTE(LEFT(A1,31)," ","#",LEN(LEFT(A1,31))-LEN(SUBSTITUTE(LEFT(A1,31)," ",""))))-1))
C1:C3C1=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:D3D1=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.

VBA Code:
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
Thanks for the coding but it picks up only 30 rows in a sheet
 

vishesh10667

New Member
Joined
Nov 26, 2019
Messages
18
Office Version
2007
Platform
Windows
=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))
Thanks but this formula is not working on my excel. It just got paste as it is. It doesn't picking the value
 

Watch MrExcel Video

Forum statistics

Threads
1,099,747
Messages
5,470,531
Members
406,705
Latest member
JBram

This Week's Hot Topics

Top