+ Reply to Thread
Results 1 to 9 of 9

How to split values separated by "," into different raws?

  1. #1
    Registered User
    Join Date
    10-29-2018
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    21

    How to split values separated by "," into different raws?

    Hi!
    I need to split two or more values that are contained in one cell into different raws for a new table.
    thanks in advance for any help!
    Attached Files Attached Files

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: How to split values separated by "," into different raws?

    Hi,

    Have you tried the "text to columns" option under the "Data" menu?

    Select delimited text and use the comma as a delimiter,
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: How to split values separated by "," into different raws?

    Please try at B46 and copy down for maximum 5 values in one cell

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


    or textjoin for Excel 365
    =TRIM(MID(SUBSTITUTE(Textjoin(", ",,$C$36:$C$42),", ",REPT(" ",100)),(ROWS(B$46:B46)-1)*100+1,100))
    Attached Files Attached Files
    Last edited by Bo_Ry; 11-22-2018 at 11:28 AM. Reason: corrected formula

  4. #4
    Registered User
    Join Date
    10-29-2018
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    21

    Re: How to split values separated by "," into different raws?

    Hi, thanks for answering!
    Yes, I have tried that but it doesn't work bucause the values I want to split are brought from another table through a formula. When I try to use this option, it selects the formula and not the data contained in the cell.

  5. #5
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: How to split values separated by "," into different raws?

    Then you have a couple of options:

    You could copy and paste special > values and then use text to columns,

    or use Bo_Ry's formula (@Bo-Ry - nice work!)

  6. #6
    Registered User
    Join Date
    10-29-2018
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    21

    Re: How to split values separated by "," into different raws?

    Bo_Ry.

    Thank you for the answer. But it doesn't work for my file. Can you please explein me why you used this "$A$1:$E$1"
    Last edited by jeffreybrown; 11-24-2018 at 10:29 PM.

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: How to split values separated by "," into different raws?

    I split text in 1 cell to 5 columns from {1,50,100,150,200} so I need array of 5 column($A$1:$E$1) = {1,2,3,4,5} to get the same dimension.

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: How to split values separated by "," into different raws?

    Oh! Excel 2007 doesn't have aggregate

    Try this with Ctrl+Shift+Enter

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

  9. #9
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: How to split values separated by "," into different raws?

    If one does not have the benefit of Excel 365 (this is no improvement on Bo's Excel 365 approach) and one is prepared to accept a helper column then the above formula can be significantly simplified and the limitation of no more than 5 comma separated elements can be removed. This approach works in (at least) all Excel versions 2017 and more recent.

    With reference to the attached spreadsheet the helper range is H36:H42. Enter the following (non-array) formula into H36 and fill down to H42:

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

    This formula concatenates the contents of the "Change" range C36:C42 into the final cell of the helper range H42 with each element from C36:C42 separated with a comma. The "substitute" eliminates instances of double commas originating from blank lines in the "Change" range C36:C42.

    Now enter the following (non-array) formula into B46 and fill down as far as you wish.

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

    This formula takes the comma separated list in H42 and places each element on its own row in the B column which is what was required in the original problem description.
    Attached Files Attached Files
    Last edited by GeoffW283; 11-24-2018 at 10:02 PM.

+ 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. Split Dash "-" within values
    By bjnockle in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-22-2015, 10:26 AM
  2. [SOLVED] Text strings separated with "/", ",", "-" or "&"
    By alice2011 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-27-2015, 02:13 PM
  3. Replies: 0
    Last Post: 01-09-2013, 06:52 PM
  4. Split cell values based on "," and "-" and format as shown below
    By Roop in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-22-2012, 12:28 AM
  5. Replies: 3
    Last Post: 02-16-2011, 02:55 PM
  6. [SOLVED] How do I split "A1B2" into "A1" and "B2" using text to column fun.
    By Jennifer in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-02-2005, 06:06 PM

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