Break text in Cell into multiple cells

[zinzah]

Morning! Not the best title for this thread...but here is what i am looking to do.

I have a cell full of test. say 500 characters.
I have a another program that i need to get this text into, but i have limitations regarding characters per line.
My limitations are 36 lines with 42 characters per line

i am trying to break the text at the last space before the 42nd character

In Cell A1:
Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.


I have played with Find and Substitute and managed to get the first line correct:

Using Column A to find the correct space to start at (+1), and column B to return the text (MID)

in A6: 42 (the max length of the line)
in A7: =FIND("*",SUBSTITUTE(A1," ","*",A$6-LEN(SUBSTITUTE(LEFT(A1,42)," ","")))) which find the last space before the 42nd character (40)
in B7: =MID(A1,1,A7) which returns "Lorem Ipsum is simply dummy text of the"

Perfect!
My problem, is now getting the following lines correct

in A8: =FIND("*",SUBSTITUTE(MID(A1,A7+2,LEN(A1)-A7)," ","*",A$7-LEN(SUBSTITUTE(LEFT(A1,42)," ","")))) which returns 46, which is one space too far
in B8: =MID(A1,A7+1,A8) which returns "printing and typesetting industry. Lorem Ipsum" - this is 46 characters long. It should be less than 42 and stop at the space after "Lorem"

any pre-weekend help would be appreciated!!

 

[Akuini]

Are you ok to use vba?
Try this:

Sub a1125011a()
'https://www.mrexcel.com/board/threads/break-text-in-cell-into-multiple-cells.1125011/
Dim tx, vb
Dim g As Long, xz As Long, x As Long

tx = Range("A1")
ReDim vb(1 To 10000, 1 To 1)
xz = 42  'change to suit

        Do
            x = InStrRev(tx, " ", xz, vbTextCompare)
            g = g + 1
            If x = 0 Then vb(g, 1) = tx: Exit Do
            
            vb(g, 1) = Mid(tx, 1, x)
            tx = Mid(tx, x + 1)
        Loop Until x = 0
    
    
Range("A2").Resize(g) = vb
End Sub

Example:

Book1
	A
1	Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.
2	Lorem Ipsum is simply dummy text of the
3	printing and typesetting industry. Lorem
4	Ipsum has been the industry's standard
5	dummy text ever since the 1500s, when an
6	unknown printer took a galley of type and
7	scrambled it to make a type specimen
8	book. It has survived not only five
9	centuries, but also the leap into
10	electronic typesetting, remaining
11	essentially unchanged. It was popularised
12	in the 1960s with the release of Letraset
13	sheets containing Lorem Ipsum passages,
14	and more recently with desktop publishing
15	software like Aldus PageMaker including
16	versions of Lorem Ipsum.
Sheet3

My limitations are 36 lines

If the result is actually more than 36 row then what should happen? Do you want to remove the rest?

 

[zinzah]

Thanks for the reply.
VB is fine. I prefer it!!
Will test it when i have a few minutes!
And i am going to add a check when the initial text is entered into A1 if it is longer than the max characters allowed (1638)

Will let you know how i make out!

 

[zinzah]

Fantastic! Works great!!! I so appreciate your work!!

Thanks

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.

 

[BathAntelope]

Are you ok to use vba?
Try this:

Sub a1125011a()
'https://www.mrexcel.com/board/threads/break-text-in-cell-into-multiple-cells.1125011/
Dim tx, vb
Dim g As Long, xz As Long, x As Long

tx = Range("A1")
ReDim vb(1 To 10000, 1 To 1)
xz = 42  'change to suit

        Do
            x = InStrRev(tx, " ", xz, vbTextCompare)
            g = g + 1
            If x = 0 Then vb(g, 1) = tx: Exit Do
           
            vb(g, 1) = Mid(tx, 1, x)
            tx = Mid(tx, x + 1)
        Loop Until x = 0
   
   
Range("A2").Resize(g) = vb
End Sub

Example:

Book1
	A
1	Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. FedLoan Servicing It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.
2	Lorem Ipsum is simply dummy text of the
3	printing and typesetting industry. Lorem
4	Ipsum has been the industry's standard
5	dummy text ever since the 1500s, when an
6	unknown printer took a galley of type and
7	scrambled it to make a type specimen
8	book. It has survived not only five
9	centuries, but also the leap into
10	electronic typesetting, remaining
11	essentially unchanged. It was popularised
12	in the 1960s with the release of Letraset
13	sheets containing Lorem Ipsum passages,
14	and more recently with desktop publishing
15	software like Aldus PageMaker including
16	versions of Lorem Ipsum.
Sheet3

If the result is actually more than 36 row then what should happen? Do you want to remove the rest?

It was a great help ! Thank you very much for sharing this !

 

[Akuini]

It was a great help ! Thank you very much for sharing this !

Your welcome. I'm glad you find the code useful.

 

[johnmpl]

Hi, to all!

Check this option with formulas:

Libro1
	A
1	Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.
2
3
4	Lorem Ipsum is simply dummy text of the
5	printing and typesetting industry. Lorem
6	Ipsum has been the industry's standard
7	dummy text ever since the 1500s, when an
8	unknown printer took a galley of type and
9	scrambled it to make a type specimen
10	book. It has survived not only five
11	centuries, but also the leap into
12	electronic typesetting, remaining
13	essentially unchanged. It was popularised
14	in the 1960s with the release of Letraset
15	sheets containing Lorem Ipsum passages,
16	and more recently with desktop publishing
17	software like Aldus PageMaker including
18	versions of Lorem Ipsum.
Hoja1

Cell Formulas
Range	Formula
A4:A18	A4	=MID(A$1,1+SUMPRODUCT(LEN(A$3:A3)),LOOKUP(1,0/(MID(A$1&" ",SUMPRODUCT(LEN(A$3:A3))+ROW($1:$42),1)=" "),ROW($1:$42)))

Blessings!

 

[Peter_SSs]

Fantastic! Works great!!! I so appreciate your work!!

Just checking.

1. When the sample text is first split between "the" and "printing" do you actually want the space between those two words retained?
That is, should the last text of the first split be "the "? Note the space after the word. Or do you want that space trimmed off so that first text ends with "the"?

2. Also, you said there is a maximum character limit of 42 but also said you wanted to split at the last space before the 42nd character?
Doing that, which is what both the code and formula suggestions so far do, means that if a word finishes exactly at the 42nd character with a space at the the 43rd character, your result for that first cell will be less than 42 characters when it could hold exactly 42 characters.

Here are 2 further code suggestions, depending on the answer to question 1 above. The codes write results to different columns for the comparison below.

Sub BreakItUp_Discard_Space()
  Dim s As String
  Dim k As Long
  Dim result As Variant
 
  Const CharsPerLine As Long = 42     '<-Change to suit
  s = Range("A1").Text
  ReDim result(1 To Len(s) / CharsPerLine + 1, 1 To 1)
  k = 0
  Do Until Len(s) = 0
    k = k + 1
    result(k, 1) = RTrim(Left(s, InStrRev(s & Space(CharsPerLine), " ", CharsPerLine + 1) - 1))
    s = Mid(s, Len(result(k, 1)) + 2)
  Loop
  Range("D2").Resize(k).Value = result
End Sub

Sub BreakItUp_Retain_Space()
  Dim s As String
  Dim k As Long, pos As Long
  Dim result As Variant
 
  Const CharsPerLine As Long = 42     '<-Change to suit
  s = Range("A1").Text
  ReDim result(1 To Len(s) / CharsPerLine + 1, 1 To 1)
  k = 0
  Do Until Len(s) = 0
    k = k + 1
    pos = InStrRev(s & Space(CharsPerLine), " ", CharsPerLine + 1)
    If pos > CharsPerLine Then pos = CharsPerLine
    result(k, 1) = Left(s, pos)
    s = Mid(s, Len(result(k, 1)) + 1)
  Loop
  Range("G2").Resize(k).Value = result
End Sub

Comparison of results - note that I have modified the sample slightly so that a space did occur at position 43.

20 02 26.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Lorem Ipsum is simply dummy text of theory printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.
2	Lorem Ipsum is simply dummy text of	36	35	Lorem Ipsum is simply dummy text of theory	42	42	Lorem Ipsum is simply dummy text of theory	42	42	Lorem Ipsum is simply dummy text of	36	35
3	theory printing and typesetting industry.	42	41	printing and typesetting industry. Lorem	40	40	printing and typesetting industry. Lorem	42	40	theory printing and typesetting industry.	42	41
4	Lorem Ipsum has been the industry's	36	35	Ipsum has been the industry's standard	38	38	Ipsum has been the industry's standard	39	38	Lorem Ipsum has been the industry's	36	35
5	standard dummy text ever since the 1500s,	42	41	dummy text ever since the 1500s, when an	40	40	dummy text ever since the 1500s, when an	41	40	standard dummy text ever since the 1500s,	42	41
6	when an unknown printer took a galley of	41	40	unknown printer took a galley of type and	41	41	unknown printer took a galley of type and	42	41	when an unknown printer took a galley of	41	40
7	type and scrambled it to make a type	37	36	scrambled it to make a type specimen book.	42	42	scrambled it to make a type specimen book.	42	42	type and scrambled it to make a type	37	36
8	specimen book. It has survived not only	40	39	It has survived not only five centuries,	40	40	It has survived not only five centuries,	42	40	specimen book. It has survived not only	40	39
9	five centuries, but also the leap into	39	38	but also the leap into electronic	33	33	but also the leap into electronic	34	33	five centuries, but also the leap into	39	38
10	electronic typesetting, remaining	34	33	typesetting, remaining essentially	34	34	typesetting, remaining essentially	35	34	electronic typesetting, remaining	34	33
11	essentially unchanged. It was popularised	42	41	unchanged. It was popularised in the 1960s	42	42	unchanged. It was popularised in the 1960s	42	42	essentially unchanged. It was popularised	42	41
12	in the 1960s with the release of Letraset	42	41	with the release of Letraset sheets	35	35	with the release of Letraset sheets	37	35	in the 1960s with the release of Letraset	42	41
13	sheets containing Lorem Ipsum passages,	40	39	containing Lorem Ipsum passages, and more	41	41	containing Lorem Ipsum passages, and more	42	41	sheets containing Lorem Ipsum passages,	40	39
14	and more recently with desktop publishing	42	41	recently with desktop publishing software	41	41	recently with desktop publishing software	42	41	and more recently with desktop publishing	42	41
15	software like Aldus PageMaker including	40	39	like Aldus PageMaker including versions of	42	42	like Aldus PageMaker including versions of	42	42	software like Aldus PageMaker including	40	39
16	versions of Lorem Ipsum.	24	24	Lorem Ipsum.	12	12	Lorem Ipsum.	13	12	versions of Lorem Ipsum.	24	24
17
18	Post #2	577	563	Post #9 Code 1	563	563	Post #9 Code 2	577	563	Post #8	577	563
Split text

Cell Formulas
Range	Formula
B2:B16,H2:H16,K2:K16,E2:E16	B2	=LEN(A2)
C2:C16,I2:I16,L2:L16,F2:F16	C2	=LEN(TRIM(A2))
J2:J16	J2	=MID(A$1,1+SUMPRODUCT(LEN(J$1:J1)),LOOKUP(1,0/(MID(A$1&" ",SUMPRODUCT(LEN(J$1:J1))+ROW($1:$42),1)=" "),ROW($1:$42)))
B18:C18,K18:L18,H18:I18,E18:F18	B18	=SUM(B2:B17)