Break text in Cell into multiple cells

zinzah

New Member
Joined
Mar 9, 2010
Messages
38
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!!
:)





 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
Are you ok to use vba?
Try this:
VBA Code:
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
1Lorem 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.
2Lorem Ipsum is simply dummy text of the
3printing and typesetting industry. Lorem
4Ipsum has been the industry's standard
5dummy text ever since the 1500s, when an
6unknown printer took a galley of type and
7scrambled it to make a type specimen
8book. It has survived not only five
9centuries, but also the leap into
10electronic typesetting, remaining
11essentially unchanged. It was popularised
12in the 1960s with the release of Letraset
13sheets containing Lorem Ipsum passages,
14and more recently with desktop publishing
15software like Aldus PageMaker including
16versions 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

New Member
Joined
Mar 9, 2010
Messages
38
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!
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

BathAntelope

New Member
Joined
Feb 22, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
Are you ok to use vba?
Try this:
VBA Code:
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
1Lorem 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.
2Lorem Ipsum is simply dummy text of the
3printing and typesetting industry. Lorem
4Ipsum has been the industry's standard
5dummy text ever since the 1500s, when an
6unknown printer took a galley of type and
7scrambled it to make a type specimen
8book. It has survived not only five
9centuries, but also the leap into
10electronic typesetting, remaining
11essentially unchanged. It was popularised
12in the 1960s with the release of Letraset
13sheets containing Lorem Ipsum passages,
14and more recently with desktop publishing
15software like Aldus PageMaker including
16versions 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

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It was a great help ! Thank you very much for sharing this !
Your welcome. I'm glad you find the code useful.
 

johnmpl

Board Regular
Joined
Jun 14, 2013
Messages
235
Hi, to all!

Check this option with formulas:

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
VBA Code:
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
ABCDEFGHIJKL
1Lorem 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.
2Lorem Ipsum is simply dummy text of 3635Lorem Ipsum is simply dummy text of theory4242Lorem Ipsum is simply dummy text of theory4242Lorem Ipsum is simply dummy text of 3635
3theory printing and typesetting industry. 4241printing and typesetting industry. Lorem4040 printing and typesetting industry. Lorem 4240theory printing and typesetting industry. 4241
4Lorem Ipsum has been the industry's 3635Ipsum has been the industry's standard3838Ipsum has been the industry's standard 3938Lorem Ipsum has been the industry's 3635
5standard dummy text ever since the 1500s, 4241dummy text ever since the 1500s, when an4040dummy text ever since the 1500s, when an 4140standard dummy text ever since the 1500s, 4241
6when an unknown printer took a galley of 4140unknown printer took a galley of type and4141unknown printer took a galley of type and 4241when an unknown printer took a galley of 4140
7type and scrambled it to make a type 3736scrambled it to make a type specimen book.4242scrambled it to make a type specimen book.4242type and scrambled it to make a type 3736
8specimen book. It has survived not only 4039It has survived not only five centuries,4040 It has survived not only five centuries, 4240specimen book. It has survived not only 4039
9five centuries, but also the leap into 3938but also the leap into electronic3333but also the leap into electronic 3433five centuries, but also the leap into 3938
10electronic typesetting, remaining 3433typesetting, remaining essentially3434typesetting, remaining essentially 3534electronic typesetting, remaining 3433
11essentially unchanged. It was popularised 4241unchanged. It was popularised in the 1960s4242unchanged. It was popularised in the 1960s4242essentially unchanged. It was popularised 4241
12in the 1960s with the release of Letraset 4241with the release of Letraset sheets3535 with the release of Letraset sheets 3735in the 1960s with the release of Letraset 4241
13sheets containing Lorem Ipsum passages, 4039containing Lorem Ipsum passages, and more4141containing Lorem Ipsum passages, and more 4241sheets containing Lorem Ipsum passages, 4039
14and more recently with desktop publishing 4241recently with desktop publishing software4141recently with desktop publishing software 4241and more recently with desktop publishing 4241
15software like Aldus PageMaker including 4039like Aldus PageMaker including versions of4242like Aldus PageMaker including versions of4242software like Aldus PageMaker including 4039
16versions of Lorem Ipsum.2424Lorem Ipsum.1212 Lorem Ipsum.1312versions of Lorem Ipsum.2424
17
18Post #2577563Post #9 Code 1563563Post #9 Code 2577563Post #8577563
Split text
Cell Formulas
RangeFormula
B2:B16,H2:H16,K2:K16,E2:E16B2=LEN(A2)
C2:C16,I2:I16,L2:L16,F2:F16C2=LEN(TRIM(A2))
J2:J16J2=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:F18B18=SUM(B2:B17)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,550
Messages
5,625,455
Members
416,107
Latest member
AVaes

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
Top