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
  1. 2007
Platform
  1. 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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
Welcome to the MrExcel board!

Try these formulas copied down.

Book1
ABCD
1This cell contains enough characters to exceed the column limitThis cell contains enoughcharacters to exceed thecolumn limit
2Short textShort text  
3This should have a break after 30 charactersThis should have a break after30 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
 
Upvote 0
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
 
Upvote 0
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 limitThis cell contains enoughcharacters to exceed thecolumn limit
2Short textShort text  
3This should have a break after 30 charactersThis should have a break after30 characters 
4I have huge columns of data that can only be 30 characters longI have huge columns of datathat can only be 30 characterslong
5I want to use a formula to split the column into three.I want to use a formula tosplit the column into three. 
6The 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
7If 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
8that 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.
9The 2nd column should also be 30 characters long. And rest in 3 columnThe 2nd column should also be30 characters long. And restin 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),""))
 
Upvote 0
Welcome to the MrExcel board!

Try these formulas copied down.

Book1
ABCD
1This cell contains enough characters to exceed the column limitThis cell contains enoughcharacters to exceed thecolumn limit
2Short textShort text  
3This should have a break after 30 charactersThis should have a break after30 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
 
Upvote 0
=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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top