Single Row into Multiple Rows.

[joenjoe]

Is there a way convert single cell with wrap text into multiple cells with the number of resulting rows based on the number of lines in the original cell. Due to the restrictions of my overall printable "document", I must use multiple cells of the same height. Any change of row height would throw off the desired final result.

As an example, when I start with my original cell in text wrap and set column width I get something like the following in cell F2:

ASTM A743 UNS J93254
(SUPER AUSTENITIC)
(LONG LEAD TIME
MATERIAL)

This looks great, but it is all in one cell, and the four lines of wrapped text have obviously increased the row height which is bad.

I have tried using the LEFT and MID functions to force the text into different rows based on character length, but this is sort of messy since I am never sure what the input will be resulting in something like the following (this time in cells F2:F5) with spaces at the beginning of a line or breaks in the middle of words:

ASTM A743 UNS J93254
(SUPER AUSTENITIC)
(LONG LEAD TIME MATE
RIAL)

My ultimate goal would be to somehow format the original input data text so that regardless of the input, the text would be restricted to a certain column width, sending the remaining text to the next consecutive rows down, and maintain the integrity of the original. I can have as many intermediate cells, formatting as I go to get to this point.

Of course, I could manually enter the text in the different rows every time, but it would be nice to have a succession of formatted cells so that the desired end result would be automatic. Hopefully, the answer does not involve too much in the way of macro or any VBA code since I am a pitiful novice, and we are sort of under pressure to get this project under way.

Any assistance would be greatly appreciated.

 

[venkat1926]

see message below

 

[venkat1926]

suppose the long string is in A1
if it is not in A1 you change only the $a$1(FIRST ITEM WITHIN THE BRACKET) into some other celll with dollar signs intact.
DO NOT CHANGE OTHER ITEMS IN THE FORMULA

I configured that the column width is 8. If it is different change wherever 8 comes.

in any cell type or copy paste this formula
=MID($A$1,ROW(A1)+(ROW(A1)-1)*8,8)
and copy this formula down till you get a blank cell

see do you get what you want?

 

[Richard Schollar]

Hello Joe

Here's one way you might do this via formula - the formula in A2 is copied down as far as required (it returns blanks once there's no text to return):
<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:195px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:68px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >ASTM A743 UNS J93254<br />(SUPER AUSTENITIC)<br />(LONG LEAD TIME<br />MATERIAL)</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >ASTM A743 UNS J93254</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >(SUPER AUSTENITIC)</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >(LONG LEAD TIME</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >MATERIAL)</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A2</td><td >=TRIM(MID<span style=' color:008000; '>(SUBSTITUTE<span style=' color:#0000ff; '>(CHAR<span style=' color:#ff0000; '>(10)</span>&TRIM<span style=' color:#ff0000; '>($A$1)</span>,CHAR<span style=' color:#ff0000; '>(10)</span>,REPT<span style=' color:#ff0000; '>(" ",100)</span>)</span>,ROWS<span style=' color:#0000ff; '>($1:1)</span>*100+<span style=' color:#0000ff; '>(SUMPRODUCT<span style=' color:#ff0000; '>(LEN<span style=' color:#804000; '>($A$1:$A1)</span>)</span>-LEN<span style=' color:#ff0000; '>($A$1)</span>)</span>,100)</span>)</td></tr></table></td></tr></table>

I note that you have posted this at Ozgrid.com - please in future could you indicate that you have cross-posted this at another forum(s), as this will prevent members from then wasting their time answering a post which may have already been answered on another forum. We at MrExcel are not as strict as other forums (where you may get banned for cross-posting), but we do appreciate being informed that it has been cross-posted.

 

[francisp]

It is very simple. Instead of writing formulas etc. Just select the cell which you want to extend below. Now Select more cells below where you want to extend. Now click Edit on the main Menu. Select Fill & then Select Justify....the single row will now extend to multiple row. Plain simple.

 

[francisp]

Please remember to change the original cell from Wrap text to normal, before you do the Edit, Fill , justify commands.

 

[joenjoe]

Thank you everyone for the replies. Richard, I apologize for "cross-posting". In my haste to register, I confess that I breezed through the rules and did not realize that this was a violation. However, I can see where it would be frustrating to spin your wheels answering a question that someone else has already addressed on another post. I will avoid doing this in the future.

As for your suggested formula to solve my problem, the graphic you included seems to be exactly what I'm looking for. Forgive my ignorance, but when I simply copy and paste the formula into successive rows I do not get your same results. Cell A2 shows exactly what yours did (which is perfect so far), but A3 also shows the same thing, not the next portion. Do I need to alter the formula as I go down each row? Thanks.

 

[Richard Schollar]

Copy the formula into cell A2 and then copy cell A2 and select the cells below it (eg A3:A10) and click Paste. Make sure that you have Automatic calculation enabled (via Tools>Options>Calculation tab). It should then work as indicated.

 

[joenjoe]

Thanks Richard. Unfortunately, I am still not getting the same results even though I am following your example precisely. I did confirm that Automatic calculation is enabled. All of the cells are formatted to allow text wrap.

I am attempting to use "Excel Jeanie" to illustrate my results below. Actually, my rows from A2 on down have heights set to 12.75 to allow for only 1 row of text to show. My Excel Jeanie preview shows that A2 has expanded although this does not reflect my actual results. I am happy with my A2 results as a start if only the following cells would be behave in kind.

Either way, as you can see, A3 and on are blank. Your formula seems as though it would be ideal for my purposes if only I could achieve the same results. Can you imagine that I have some setting or another yet to alter?

(Since this is my first use of Excel Jeanie, I hope it translate well into the forum once I hit the "Post" button.)

Excel Workbook
	A
1	ASTM A743 UNS J93254 (SUPER AUSTENITIC) (LONG LEAD TIME MATERIAL)
2	ASTM A743 UNS J93254 (SUPER AUSTENITIC) (LONG LEAD TIME MATERIAL)
3
4
5
Sheet1

 

[Richard Schollar]

Ah, I think this is my fault: I assumed you had line feed characters marking where the new rows (within the text-wrapped cell) should be - if this isn't the case, you will get output as you have seen.

Without the linefeed (or carriage return character) you're going to have real trouble separating the text as you wanted. You could split it up every, say, 50 characters, but that wouldn't ensure you have whole words etc - if the 50th place split a word in two, you'd end up with half a word on one line, and half on the next.

If you can live without a formula, then Francisp's approach is probably the best (and easiest).