Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Single Row into Multiple Rows.

This is a discussion on Single Row into Multiple Rows. within the Excel Questions forums, part of the Question Forums category; Is there a way convert single cell with wrap text into multiple cells with the number of resulting rows based ...

  1. #1
    New Member
    Join Date
    Jan 2005
    Location
    Milwaukee, Wisconsin
    Posts
    25

    Default Single Row into Multiple Rows.

    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.

  2. #2
    Board Regular
    Join Date
    Aug 2005
    Posts
    4,687

    Default Re: Single Row into Multiple Rows.

    see message below
    Last edited by venkat1926; Dec 25th, 2007 at 03:29 AM.
    I am not an expert. So better solutions may be available
    MinE WINDOWS 7 AND excel 2007(compatbililty mode)
    venkat1926(at)gmail(dot)com
    preferably do not send private messages in the newsgroup reply to newsgroup

  3. #3
    Board Regular
    Join Date
    Aug 2005
    Posts
    4,687

    Default Re: Single Row into Multiple Rows.

    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?
    Last edited by venkat1926; Dec 25th, 2007 at 03:31 AM.
    I am not an expert. So better solutions may be available
    MinE WINDOWS 7 AND excel 2007(compatbililty mode)
    venkat1926(at)gmail(dot)com
    preferably do not send private messages in the newsgroup reply to newsgroup

  4. #4
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,673

    Default Re: Single Row into Multiple Rows.

    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):
    Sheet2

     A
    1ASTM A743 UNS J93254
    (SUPER AUSTENITIC)
    (LONG LEAD TIME
    MATERIAL)
    2ASTM A743 UNS J93254
    3(SUPER AUSTENITIC)
    4(LONG LEAD TIME
    5MATERIAL)
    6 
    7 
    8 

    Spreadsheet Formulas
    CellFormula
    A2=TRIM(MID(SUBSTITUTE(CHAR(10)&TRIM($A$1),CHAR(10),REPT(" ",100)),ROWS($1:1)*100+(SUMPRODUCT(LEN($A$1:$A1))-LEN($A$1)),100))


    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.
    Richard Schollar

    Using xl2013

  5. #5
    New Member
    Join Date
    Apr 2003
    Location
    India
    Posts
    41

    Red face Re: Single Row into Multiple Rows.

    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.

  6. #6
    New Member
    Join Date
    Apr 2003
    Location
    India
    Posts
    41

    Default Re: Single Row into Multiple Rows.

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

  7. #7
    New Member
    Join Date
    Jan 2005
    Location
    Milwaukee, Wisconsin
    Posts
    25

    Default Re: Single Row into Multiple Rows.

    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.

  8. #8
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,673

    Default Re: Single Row into Multiple Rows.

    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.
    Richard Schollar

    Using xl2013

  9. #9
    New Member
    Join Date
    Jan 2005
    Location
    Milwaukee, Wisconsin
    Posts
    25

    Default Re: Single Row into Multiple Rows.

    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.)

    Sheet1

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

    Spreadsheet Formulas
    CellFormula
    A2=TRIM(MID(SUBSTITUTE(CHAR(10)&TRIM($A$1),CHAR(10),REPT(" ",100)),ROWS($1:1)*100+(SUMPRODUCT(LEN($A$1:$A1))-LEN($A$1)),100))
    A3=TRIM(MID(SUBSTITUTE(CHAR(10)&TRIM($A$1),CHAR(10),REPT(" ",100)),ROWS($1:2)*100+(SUMPRODUCT(LEN($A$1:$A2))-LEN($A$1)),100))
    A4=TRIM(MID(SUBSTITUTE(CHAR(10)&TRIM($A$1),CHAR(10),REPT(" ",100)),ROWS($1:3)*100+(SUMPRODUCT(LEN($A$1:$A3))-LEN($A$1)),100))
    A5=TRIM(MID(SUBSTITUTE(CHAR(10)&TRIM($A$1),CHAR(10),REPT(" ",100)),ROWS($1:4)*100+(SUMPRODUCT(LEN($A$1:$A4))-LEN($A$1)),100))


    Excel tables to the web >> Excel Jeanie HTML 4

  10. #10
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,673

    Default Re: Single Row into Multiple Rows.

    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).
    Richard Schollar

    Using xl2013

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com