+ Reply to Thread
Results 1 to 14 of 14

splitting cell contents into new rows

  1. #1
    Registered User
    Join Date
    12-17-2009
    Location
    Kirkcaldy, Scotland
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2201) 64-bit
    Posts
    75

    splitting cell contents into new rows

    Hoping someone can help me with this one. I have a spreadheet similar to the attached.

    I'm basically trying to find a way to make the contents in columns A&B run to show as those in columns E&F?
    Pretty sure this will be an easy fix/formula for someone out there (unfortunately not for me). Any suggestions greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: splitting cell contents into new rows

    VBA would be an easier solution than a formula
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    12-17-2009
    Location
    Kirkcaldy, Scotland
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2201) 64-bit
    Posts
    75

    Re: splitting cell contents into new rows

    Sorry Special-K I'm not great when it comes to Excel - are you able to advise wha VBA is and how I could use it to work on the sample data?

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: splitting cell contents into new rows

    Either your input data is wrong or your results are wrong.

    B6 is DISPLAYING "101,127" (implying two numbers separated by a comma from your previous values in that column) but the actual content of that cell is 101127 which is one number formatted to display a comma. However, you have produced two values for that one cell, 101 and 127.
    If the original value is meant to be 101127 then the output should be 101127, not 101 and 127.
    If the original value is meant to be 101 and 127 then there should be a comma in the data which 101127 does not contain.

    Nevertheless this works:

    Please Login or Register  to view this content.
    To enter this press Alt-F11.
    Double click Sheet1
    Paste the code into the right hand pane

    Just below the "Debug" menu is a green triangle, click this to run the code.

    Go to Sheet1 and the output will be as requested (once you have decided upon the "10127" problem above)

  5. #5
    Registered User
    Join Date
    12-17-2009
    Location
    Kirkcaldy, Scotland
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2201) 64-bit
    Posts
    75

    Re: splitting cell contents into new rows

    That appears to work perfect - except for the missing comma which I don't understand. It is showing as there on the spreadsheet but when I click in the cell it is just one long number. Any idea how to fix this (if so you have found me the perfect solution)? It seems to work in some cells but not in others. It seems to work on any cell starting with a one or two digit number (eg 63,65,123) but not for anything starting with a 3 digit number (rg 123,125)?
    Last edited by grim72; 08-22-2017 at 11:20 AM.

  6. #6
    Registered User
    Join Date
    12-17-2009
    Location
    Kirkcaldy, Scotland
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2201) 64-bit
    Posts
    75

    Re: splitting cell contents into new rows

    Looking at it I think it reading the comma as a '000 seperator rather than an actual comma but I can't figure out how to fix that?

  7. #7
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: splitting cell contents into new rows

    That cell is formatted to insert a comma for values over 1000.
    But the other cells are formatted as General.

    If you insert a space after each comma this will allow you to enter that cell as two numbers.
    However you may need to amend the code

    Please Login or Register  to view this content.
    and

    Please Login or Register  to view this content.
    Note: Space after comma

  8. #8
    Registered User
    Join Date
    12-17-2009
    Location
    Kirkcaldy, Scotland
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2201) 64-bit
    Posts
    75

    Re: splitting cell contents into new rows

    Will I need to do this manually for each cell? I tried to do a Ctrl+H to replace "," with ", " but it didnt do it for the ones with the 000 seperator? Sorry if I'm being useless, this is sending me crazy as it's so close to being right but can't figure how to convert the number to general format to include the comma?

  9. #9
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: splitting cell contents into new rows

    It depends how you got the data into the cells in the first place.

    You should decide upon a format for ALL the cells in the column and keep them the same, General looks best.

    Excel stores numbers as their values. Formatting a cell does not change the value of the number, only the way in which it is displayed.
    So if you search for a comma it will only find commas you have physically entered, not commas that Excel has displayed because of a cell's format.

    General format doesn't display a comma.

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: splitting cell contents into new rows

    Use column C as a temporary helper.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Copy and paste values back unto themselves.

    Trailing commas keep the returns from reverting back to numeric values.

    You should be able to deal with those commas same as the others.

    Replace the original data with those.
    Last edited by FlameRetired; 08-23-2017 at 01:00 AM. Reason: details and formula rewrite
    Dave

  11. #11
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: splitting cell contents into new rows

    Create two helper columns

    A2=1

    A3=A2+D2

    D2=SUMPRODUCT(--(MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1)=","))+1

    I2=IFERROR(VLOOKUP(IF(ROWS(I$2:I2)>SUM($D$2:$D$6)+1,"",ROWS(I$2:I2)),$A$2:$B$6,2),"")

    J2=IFERROR(AGGREGATE(15,6,1/(1/MID(VLOOKUP(I2,$B$2:$C$6,2,0),ROW(INDIRECT("1:"&LEN(VLOOKUP(I2,$B$2:$C$6,2,0)))),1)),COUNTIFS(I$2:I2,I2)),"")

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    Helper 1 Product ID Category ID Helper 2 Product ID Category ID Product ID Category ID
    2
    1
    1
    1,2,3,4
    4
    1
    1
    1
    1
    3
    5
    2
    2,5,6,9
    4
    1
    2
    1
    2
    4
    9
    3
    1,7
    2
    1
    3
    1
    3
    5
    11
    5
    8
    1
    1
    4
    1
    4
    6
    12
    7
    101,127
    1
    2
    2
    2
    2
    7
    2
    5
    2
    5
    8
    2
    6
    2
    6
    9
    2
    9
    2
    9
    10
    3
    1
    3
    1
    11
    3
    7
    3
    7
    12
    5
    8
    5
    8
    13
    7
    101
    7
    1
    14
    7
    127
    7
    1
    15
    16
    17
    18


    Note Product id 7's category Id 101,127 is in number format and doesn't have , separator so it would not produce correct result.

    Check the attached file.
    Attached Files Attached Files
    Last edited by shukla.ankur281190; 08-23-2017 at 01:35 AM.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: splitting cell contents into new rows

    If you're interested in a formula solution the setup is a bit convoluted.

    In column B the Category IDs are the result of post #10 above.
    In column C is a count of the Category IDs.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Insert number of columns to equal the max count. Then in D2 down and across to return Count number of Product ID's.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In column I the summary Product IDs.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In column J the parsed Category IDs.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    12-17-2009
    Location
    Kirkcaldy, Scotland
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2201) 64-bit
    Posts
    75

    Re: splitting cell contents into new rows

    Thanks for everyone's help - I got there and got he solution I needed.

  14. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: splitting cell contents into new rows

    You're welcome. Thanks for the feedback, and for marking this thread Solved. It helps.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula for splitting cell contents into two rows?
    By sccrbrg in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-28-2014, 05:56 PM
  2. splitting contents of a column to multiple rows
    By jjame0 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-13-2013, 03:40 AM
  3. [SOLVED] Splitting cell contents based on Comma and inserting new rows below it.
    By SS113 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-22-2013, 09:09 AM
  4. [SOLVED] Splitting contents of 2 cells into rows
    By blackls1z in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 08-22-2012, 09:36 AM
  5. Excel 2007 : Help with splitting contents of a cell
    By BigGPL in forum Excel General
    Replies: 4
    Last Post: 01-22-2012, 07:21 AM
  6. Splitting the contents of a cell
    By sheargraphix in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-14-2011, 07:11 AM
  7. Duplicating rows and splitting cell contents
    By levycraig in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-25-2010, 05:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1