macro or formula to break text up to fit column width 86

[tonywatsonhelp]

Hi Everyone,

So I have a list of information in excel that i need broken up but cant think how to do it?
so Col;umn C hold my current T&C as lots of rows of data.
some and just short sentances that fit nicely into my sheet but the problem is when i get long sentences / content in one cell.
i either set it to text wrap which makes th rows expand messing up the look of the page as there are other things in columns next to it or i turn off text wrap and one the text goes past the end of the cell i loss it
so i want to break my text up.
its a one off thing so i don't mind if it a formula and i have to play around with it or it a macro that just does it but this is what i want.

I have put the Text into a clean sheet that just holds the data down column A the column width is 86 and the text size is calibri 8.5
id like split up the cells so no text goes past the end of the cell put the extra text underneath the row it was taken from
some of these will be text rows will need multiple rows to get all the data in
i don't mind if its a copy put into a new sheet (I have one called "New") or just next to the current data in couln b or c or even edited as in place i just need the result.
please help if you can
Thanks
Tony

 

[StuLux]

If you establish how many characters you want to display in each cell (say 86) then by adding a row above with multiples of this number you can split the text easily in to chunks of (say 86) characters.

Book1
	A	B	C	D	E	F	G	H	I	J
1	TEXT	86	172	258	344	430	516	602	688	774
2	Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Tincidunt lobortis feugiat vivamus at. Massa vitae tortor condimentum lacinia quis vel eros donec ac. In ante metus dictum at. Ultricies tristique nulla aliquet enim tortor at. Libero nunc consequat interdum varius sit amet mattis vulputate. Enim praesent elementum facilisis leo vel fringilla. Ipsum suspendisse ultrices gravida dictum fusce ut. Facilisi nullam vehicula ipsum a arcu cursus vitae congue. Faucibus nisl tincidunt eget nullam non nisi est sit. Porta lorem mollis aliquam ut porttitor leo. Bibendum est ultricies integer quis auctor elit sed vulputate mi. Amet tellus cras adipiscing enim eu. In massa tempor nec feugiat nisl. Enim nunc faucibus a pellentesque sit amet. Nec nam aliquam sem et tortor consequat id. Dolor sit amet consectetur adipiscing elit pellentesque habitant. Luctus venenatis lectus magna fringilla urna. Fames ac turpis egestas maecenas pharetra. Nulla facilisi etiam dignissim diam quis enim lobortis scelerisque. Mi tempus imperdiet nulla malesuada. Nunc faucibus a pellentesque sit.	Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incidid	Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incidid	Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incidid	Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incidid	Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incidid	Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incidid	Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incidid	Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incidid	Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incidid
Sheet1

Cell Formulas
Range	Formula
C1:J1	C1	=B1+86
B2:J2	B2	=LEFT(A2,B1)

 

[tonywatsonhelp]

thanks Stu,
this is good but it breaks the words in half is a word is say 84 to 90,
what i could do with is it breaking up the text but if the 86 is a word go back to space and cut from there,
any ideas how i could do this anyone?
Thanks
Tony

 

[StuLux]

Sorry, I've tried several different things with this but not getting anywhere - anybody else?

 

[Akuini]

Try this:

Sub a1122681a()
'https://www.mrexcel.com/board/threads/macro-or-formula-to-break-text-up-to-fit-column-width-86.1122681/#post-5418645
Dim i As Long, j As Long, n As Long
Dim g As Long, xz As Long, x As Long
Dim va, vb

n = Range("A" & Rows.Count).End(xlUp).Row
va = Range("A2:A" & n)
ReDim vb(1 To n, 1 To 100)
xz = 86  'change to suit

For i = 1 To n - 1
    If Len(va(i, 1)) > xz Then
        g = 0
        Do
            x = InStrRev(va(i, 1), " ", xz, vbTextCompare)
            If x = 0 Then vb(i, g + 1) = va(i, 1): Exit Do
            g = g + 1
            vb(i, g) = Mid(va(i, 1), 1, x)
            va(i, 1) = Mid(va(i, 1), x + 1)
        
        Loop Until x = 0
    
    Else
    vb(i, 1) = va(i, 1)
    End If
    
Next

Range("B2").Resize(n, 100) = vb

End Sub

EXAMPLE:

Book1
	A	B	C	D
1
2	Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Tincidunt lobortis feugiat vivamus at. Massa vitae tortor condimentum lacinia quis vel eros donec ac.	Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor	incididunt ut labore et dolore magna aliqua. Tincidunt lobortis feugiat vivamus at.	Massa vitae tortor condimentum lacinia quis vel eros donec ac.
3	magna aliqua.	magna aliqua.
4	asd	asd
Sheet1