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

#### vishesh10667

##### New Member
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?

### 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
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
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
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
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
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``````

• zvcknvrv

#### KOKOSEK

##### Board Regular
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
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
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
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
=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