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

 

[KOKOSEK]

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]

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]

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]

	A​	B​	C​	D​
2​	I 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
3​	I want to use a formula to split the column into three.	I want to use a formula to	split the column into	three.
4​	The 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
5​	If 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
6​	that 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.
7​	The 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

I've used UDF like below (please paste it into VBA module):

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]

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

 

[KOKOSEK]

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 long	I have huge columns of data	that can only be 30	characters long	I have huge columns of data	hat can only be 30 characters	characters long
3​	I want to use a formula to split the column into three.	I want to use a formula to	split the column into	three.	I want to use a formula to	plit the column into three.	three.
4​	The 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	The original column will have	character limit of 30, and	should not cut words in half
5​	If 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	If the Length will be more	han 30 while including the	next 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 first	displayed in adjacent cell in	the 2nd column.	that word should be the first	isplayed in adjacent cell in	the 2nd column.
7​	The 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	The 2nd column should also be	0 characters long. And rest in	in 3 column

 

[Peter_SSs]

Peter_SSs: formula from middle column (C1) cut of first letter from string. Look:

Looks okay to me.

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
4	I 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
5	I want to use a formula to split the column into three.	I want to use a formula to	split the column into three.
6	The 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
7	If 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
8	that 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.
9	The 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
Range	Formula
B1:B9	B1	=IF(LEN(A1)<=30,A1,LEFT(A1,FIND("#",SUBSTITUTE(LEFT(A1,31)," ","#",LEN(LEFT(A1,31))-LEN(SUBSTITUTE(LEFT(A1,31)," ",""))))-1))
C1:C9	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:D9	D1	=TRIM(SUBSTITUTE(A1,TRIM(B1&" "&C1),""))

 

[vishesh10667]

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

Thanks for the coding but it picks up only 30 rows in a sheet

 

[vishesh10667]

=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