Remove Text Beyond Column Width And Add It To The Next Row Below

[miketurn]

Hello Everyone,

I believe this one will be a challenge, but if anyone is interested...

NOTES:
1.) I am looking for this to be done in Column A, but to ANY row selected, the row numbers in the example below are just an example.
2.) Ideally I would like this to be able to be done to both single or multiple SELECTED rows, but if too complex, just single rows will be fine.

EXAMPLE #1
BEFORE
|__________A__________|_________B_________|
1 apple orange banana grapes pear
2 mango
3

If you select A1 and apply this macro you get the following

AFTER
|__________A__________|_________B_________|
1 apple orange banana
2 grapes pear mango
3

As you can see it removes the space in front of "grapes pear" and adds a space after it and then adds it to the beginning of the next rows text, which in this case is "mango".



EXAMPLE #2
BEFORE
|__________A__________|_________B_________|
1 apple orange banana grapes pear
2
3

If you select A1 and apply this macro you get the following

AFTER
|__________A__________|_________B_________|
1 apple orange banana
2 grapes pear
3

If there is no data in the next row, it removes the space in front of "grapes pear" and just adds it to the next row.


As I mention above, ideally I would be able to apply this to MULTIPLE selected rows, but as you can see, this might get too complex.

If anyone has any questions, please let me know.
Thank You to whoever reads this and to anyone who tries to create something.

 

[SpillerBD]

Some of the challenges to deal with:
The wrapping you wish to accomplish is dependent on several variables, Column width, Font, Font Size, Font Style, font kerning.
Font Kerning is the difficult one because that is where the variability occurs.
IF you force a Font such as Courier, which is a fixed width font, a macro to determine breaks could be worked out to create a variable length array that could then be pasted back into the column.

(Personally, I'm not keen on the use or purpose of such a strategy as it seems contrary to the basic table structure of Excel.)

 

[My Aswer Is This]

I believe this is a similar request you have posted before and no one has been able to provide a way to do what your asking for. But I will continue to monitor this to see if you get an answer.

Would you care to explain how and why you get several lines of text in one cell and then want to split out the text into cells below depending on column width. And then if below cells already have data insert new rows.

 

[Rick Rothstein]

I believe this is a similar request you have posted before and no one has been able to provide a way to do what your asking for. But I will continue to monitor this to see if you get an answer.

It is a little different... in his other thread, the OP was dealing with the text in individual cells whereas in this thread the OP wants to concatenate the text from all of the selected cells and split/justify them down as if that text was all in the top cell of the selection. The OP also posted a solution he found... that solution introduced me to the Justify method of a Range object (not the Justify functionality that is part of Cell Formatting).

 

[Rick Rothstein]

Hello Everyone,

I believe this one will be a challenge, but if anyone is interested...

NOTES:
1.) I am looking for this to be done in Column A, but to ANY row selected, the row numbers in the example below are just an example.
2.) Ideally I would like this to be able to be done to both single or multiple SELECTED rows, but if too complex, just single rows will be fine.

EXAMPLE #1
BEFORE
|__________A__________|_________B_________|
1 apple orange banana grapes pear
2 mango
3

If you select A1 and apply this macro you get the following

AFTER
|__________A__________|_________B_________|
1 apple orange banana
2 grapes pear mango
3

As you can see it removes the space in front of "grapes pear" and adds a space after it and then adds it to the beginning of the next rows text, which in this case is "mango".



EXAMPLE #2
BEFORE
|__________A__________|_________B_________|
1 apple orange banana grapes pear
2
3

If you select A1 and apply this macro you get the following

AFTER
|__________A__________|_________B_________|
1 apple orange banana
2 grapes pear
3

Make a contiguous selection in Column A and then run this macro and let us know if it does what you want or not...

[table="width: 500"]
[tr]
	[td]Sub ConcatAndJustifyDown()
  Dim NumOfWords As Long, Txt As String
  If Selection.Count = 1 Then
    Txt = Selection.Value
  Else
    Txt = Join(Application.Transpose(Selection))
  End If
  NumOfWords = Len(Txt) - Len(Replace(Txt, " ", ""))
  Selection.ClearContents
  Selection(1).Value = Txt
  Selection(1).Offset(1).Resize(NumOfWords).Insert xlShiftDown
  Application.DisplayAlerts = False
  Selection(1).Justify
  Application.DisplayAlerts = True
  On Error Resume Next
  Selection(1).Offset(1).Resize(NumOfWords).SpecialCells(xlBlanks).Delete xlShiftUp
  On Error GoTo 0
End Sub[/td]
[/tr]
[/table]

 

[miketurn]

Hey guys sorry it took me a little while to get back on this topic, I had to think about this one a little bit more before resonding.

@My Aswer Is This
You asked why would this be needed?
I totally understand your questioning because I would imagine not many would see my methods as a good idea.
To be honest I fear that the more I explain this, the less help I will likely receive, but I will share my reasoning to see if anyone has any possible solutions.
Probably better for me to make a new separate post for this so I will let you know when it has been posted, if you are still interested.

@Rick Rothstein
Thank You for your response, I will be back in just a bit with more of a response.

 

[miketurn]

@Rick Rothstein
I appreciate your continued help, and seeing how you are the sole possessor of advanced "cell width" macro knowledge here on this forum, I am thankful to see that you are back (I just feel bad that I keep requesting your help).

Thank You for the macro you provided, it does work, so your assumption of what I am looking for is correct.
I have a couple of questions / slight modifications if you are interested, but just the fact that you have provided me with a macro that works is more than enough.

If you are however interested in some edits, just keep in mind that again I don't have a ton of macro knowledge so I don't know if my requests are easy or very difficult to achieve, so please do not take offense.

QUESTIONS / UPDATES
1.) (I would imagine this isn't possible because the selection is probably needed) Currently you have to select the cells you want to apply this macro to.
Do you know of a way that could allow the user to select a SINGLE cell that contains the text that goes over Column A width and have it be able to detect when to stop by itself? Maybe by being able to detect somehow when there is no more text to wrap to the next column (which I don't think any such coding would exist for this) OR maybe by stopping the wrapping when it detects / reaches the next empty dataless row?
Not sure yet, but maybe even if this macro could only be applied to one selected row at a time.

2.) (This one is NOT extremely important) The current macro shifts down cells in Column A to make room for wrapped text which does make sense. Oddly though if you only select two cells where the second cell does not have to wrap into a third cell, it will still insert a new row, shifting everything below down one. I noticed this by having Column A having a fill color of yellow and Column B having a fill color of black. Column A receives an extra yellow cell at the bottom when macro is applied.

3.) (This one is NOT extremely important) Sometimes I add a color fill to Column B, no text data though.
Your current macro INSERTS the wrapped text in Column A which shifts everything below it down including the Column A background fill colors.
When it does this the color filled cells in Column A go beyond the color filled cells I have in Column B.
Would it be possible to somehow when having your macro INSERT these rows into Column A, to also shift the same amount of rows in Column B as well, replicating the same fill colors applied to Column B? So say Column B was filled in the color black, black rows would be inserted in Column B in the same amount of rows that are inserted into Column A (so they would line up correctly at the bottom)

Again thank you very much for all your help.

 

[miketurn]

Just in case anyone finds this thread helpful in the future, you can also see the following thread below which has a similar application.
(Which is the thread that @My Aswer Is This makes reference to in his above post)

https://www.mrexcel.com/forum/excel-questions/980099-wrap-text-into-next-row-not-same-row.html