Wrap Text Into Next Row, Not The Same Row

miketurn

Active Member
Joined
Dec 8, 2016
Messages
268
Hello Everyone,

I would imagine that this cannot be done because I don't think Excel would be able to know how to do this but I figured I would ask just in case. (I have done some research for this as well with no positive info found)

Say you have a row of text that goes beyond its columns width by a good amount
I am looking for I would guess, some kind of Macro that would allow me to select that row, apply a macro and it would....
Take the text within the row and break it up into rows of text based on the width of the column.

I am aware that you can turn on a text wrap and it will automatically change the height of the row based on the text within it.
That is not what I am looking for, I want it to break the text down into separate rows using the columns width to know when to move the text to the next row. It would break the text down into as many rows as it needs.

Simplistic Example

So text is in COLUMN A - ROW 10 and the COLUMN A width ends right after apple

COLUMN A
ROW 10 | apple | orange banana


I want it to do this

COLUMN A
ROW 10 | apple |
ROW 11 | orange |
ROW 12 | banana |


Like I said I would imagine Excel does not have the ability to know how to do this, but maybe?
I have seen some pretty incredible macros created by others, so I figured I would take a shot to see if anyone has any ideas of how this can be done.

Thank You to anyone who reads this
 
Last edited:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
I am not sure if there is an answer for what you want or not, but your specifications for what you want are not well-defined. You say the column ends after the word apple... both words orange and banana are longer, so theoretically they are both longer than the column width... you show them fitting whereas I think the last letter or two would not be able to be accommodated. What should really happen here... should the words be split? What should happen if the column is wide enough to accommodate the full word apple, the space after it, and the first two letters of orange... where should the first break occur, on the whole word apple or two letters into the word orange?
 
Upvote 0
Have you tried Home>Editing>Fill>Justify after selecting the cell with text that overruns the column width?
 
Upvote 0
Try this:

Select the range on the row and run this Macro:

Code:
Sub Justify_Selection()
Application.DisplayAlerts = False
Dim c As Range
    For Each c In Selection
     c.Justify
    Next
End Sub
 
Upvote 0
:eek: W...T...F...

WOW!!!
"My Aswer Is This", Your Aswer Is AMAZING!

Thank You So Much For This

I literally just sat here for 20 minutes applying this macro over and over in a trance, not believing what I was seeing each time I applied it, then screaming "I don't believe this" after each application only to apply it again with a smile on my face.
Most of my requests that I post on forums are usually not possible, this one I never thought could be done, but I am now a believer that anything is possible.

This forum lacks the emoji on his knees.... bowing down of course, but if it did have such an emoji I would have put in place of this one :p.

Thank You again for your help, this means a lot, hopefully it didn't take you a ton of time to create it, just know that it is very much appreciated.
 
Upvote 0
Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.


:eek: W...T...F...

WOW!!!
"My Aswer Is This", Your Aswer Is AMAZING!

Thank You So Much For This

I literally just sat here for 20 minutes applying this macro over and over in a trance, not believing what I was seeing each time I applied it, then screaming "I don't believe this" after each application only to apply it again with a smile on my face.
Most of my requests that I post on forums are usually not possible, this one I never thought could be done, but I am now a believer that anything is possible.

This forum lacks the emoji on his knees.... bowing down of course, but if it did have such an emoji I would have put in place of this one :p.

Thank You again for your help, this means a lot, hopefully it didn't take you a ton of time to create it, just know that it is very much appreciated.
 
Upvote 0
Thank You Guys for your responses and Thank You again "My Aswer Is This" for the script you provided, I am still amazed.

I have thought of a couple tweaks to the macro written by "My Aswer Is This" above and I was wondering if it wasn't too much trouble, could someone help me add the ability to do the following....?

First off, I am by no means a scripter (although I must admit when I record a macro and it works, for that next 2-3 minutes I feel like I am the best programmer this world has ever seen!) so I don't really know how complicated my requests really are, so I will break my request into a few parts depending on how much time someone would be willing to put into it.

All the tweaks basically revolve around what if the rows after the text I want to wrap, contains text.
Currently, the text in the cells bellow it gets overwritten if I don't create enough new rows for the wrapped text to wrap into.

1.) Is it possible when this script wraps the text into the following rows (as many as it needs), that it could create NEW ROWS for each of the wrapped rows needed?

2.) If so can the NEW ROWS have the same formating as the ORIGINAL ROW? Meaning for example say the ORIGINAL ROW has a yellow fill and say a border edge on the right side of the cell, could the NEW ROWS added have the same format?

3.) Also if TWEAK #1 can be done, when it adds NEW ROWS would it be possible for it to add one more BLANK ROW at the end as like a buffer to leave a space between the NEW ROWS created (with wrapped text) and existing text in the following rows.

This next one I would imagine would be a little more complicated.

4.) In creating the BLANK ROW from TWEAK #3, would there be a way of the script being able to analyze if the following row has text in it or is already a BLANK ROW. Meaning that if the next cell contains text then TWEAK #3 would add an extra BLANK ROW, but if the next cell does not contain text then it does not add the BLANK ROW?

EXAMPLE

Before Macro Applied
1 the cat and the hat
2 EXISTING TEXT

After Macro Applied
1 the
2 cat
3 and
4 the
5 hat
6
7 EXISTING TEXT

Again, just the fact that this script was provided for me is great enough, all these tweaks would just be helpful "icing on the cake" if they are possible and someone has some free time and the skill to add them.

Thank You to anyone who reads this and for any help provided.
 
Last edited:
Upvote 0
I normally do not ask users why they want to do something I just try to give users what they ask for. But in this case I would like to ask why do you want to enter a larger amount of text into one cell and then have it justify down? Why not just click on the cell and format the text as "Wrap Text" ?

I'm not really sure how to write a script to do what you asked for in your previous post but maybe someone else would have an answer.
 
Upvote 0
Ahh, I figured these additions to the macro would be too complex to be achieved.
I understand your questioning for this, I understand it may not be a desired effect by most.
I mainly use it for personal spreadsheets, I would imagine people would be upset if this idea was used in a spreadsheet that many people had to use.

I have quite a few reasons for its use, but I won't go into a ton of them, I have a feeling the more I describe it the less additional help by others will be offered. :)

I guess the main reason is I want to keep the row heights all the same.


This macro could however have other uses as well, beyond what I am using it for, for example if someone copied the following text from a website
APPLES, BANANAS, ORANGES, GRAPES, KIWIS
and wanted to split these into a list of separate rows, they can change the width of the column and apply it creating a list, in this example, I guess a list of their favorite fruits :)

Anyways I still thank you greatly, even in its current state it is great.
As you mention maybe there are others that may be able to add to your macro, I doubt it but if there is anyone willing to give it a shot please let me know

Thanks Again
 
Last edited:
Upvote 0
Thank you for your comments and there are a lot of people here at Mr. Excel a lot smarter then me. So I will continue to monitor this thread and see if someone else has an answer. I always like learning more from others also. Take care Mike.
Ahh, I figured these additions to the macro would be too complex to be achieved.
I understand your questioning for this, I understand it may not be a desired effect by most.
I mainly use it for personal spreadsheets, I would imagine people would be upset if this idea was used in a spreadsheet that many people had to use.

I have quite a few reasons for its use, but I won't go into a ton of them, I have a feeling the more I describe it the less additional help by others will be offered. :)

I guess the main reason is I want to keep the row heights all the same.


This macro could however have other uses as well, beyond what I am using it for, for example if someone copied the following text from a website
APPLES, BANANAS, ORANGES, GRAPES, KIWIS
and wanted to split these into a list of separate rows, they can change the width of the column and apply it creating a list, in this example, I guess a list of their favorite fruits :)

Anyways I still thank you greatly, even in its current state it is great.
As you mention maybe there are others that may be able to add to your macro, I doubt it but if there is anyone willing to give it a shot please let me know

Thanks Again
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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
Back
Top