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

vishesh10667

New Member
Joined
Nov 26, 2019
Messages
14
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
 

KOKOSEK

Board Regular
Joined
Apr 8, 2019
Messages
158
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
14
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
14
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
158
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
41,790
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

Try these formulas copied down.

xl2bb.xlam
ABCD
1This cell contains enough characters to exceed the column limit
2Short text
3This should have a break after 30 characters
Split column
Cell Formulas
Range(s)Formula
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
158
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
41,790
Office Version
365
Platform
Windows
Peter_SSs: formula from middle column (C1) cut of first letter from string. Look:
Looks okay to me. :)

xl2bb.xlam
ABCD
1This cell contains enough characters to exceed the column limit
2Short text
3This should have a break after 30 characters
4I have huge columns of data that can only be 30 characters long
5I 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
7If 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.
9The 2nd column should also be 30 characters long. And rest in 3 column
Split column
Cell Formulas
Range(s)Formula
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
14
Platform
Windows
Welcome to the MrExcel board!

Try these formulas copied down.

xl2bb.xlam
ABCD
1This cell contains enough characters to exceed the column limit
2Short text
3This should have a break after 30 characters
Split column
Cell Formulas
Range(s)Formula
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
14
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
 

Forum statistics

Threads
1,078,253
Messages
5,339,110
Members
399,279
Latest member
danidanidaniel

Some videos you may like

This Week's Hot Topics

Top