How can you seperate multiple values in one cell to it's own seperate row? RRS feed

  • Question

  • Hello everyone. This is a pretty straightforward question and I will use a dummy photo of dummy data to try and explain as easily as possible.

    I'm working in several files that contain multiple part numbers in a single cell. Sometimes those numbers are separated by a comma " , "

    Sometimes they're NOT

    Sometimes the Part Numbers themselves have a " - " as part of the actual number.

    My question is there any easy way via VBA or any other method to separate those part numbers to a separate row or line item? If not this will be extremely tedious to separate manually.

    Please see below for a dummy data mockup of what I'm dealing with and thanks in advance.

    • Edited by stillanoob Wednesday, July 11, 2018 6:22 PM
    Wednesday, July 11, 2018 5:26 PM

All replies

  • My assumption is that you have (or can copy your data to) just columns A and B, with headers in row 1 and no spaces within your data set.

    Select the cells in column B and use Data / Text to columns   - Delimited, using space and comma as the delimiter, and choose "Treat consecutive delimiters as one"

    Then, your part numbers will be spread out into columns. To get them into one column, enter this into a cell in row 2, say, in I2:


    Then in J2, enter


    and copy both those formula down for (the number of columns * num rows after the Text to columns step) rows, convert the formulas to values, sort based on J and delete all the rows where J = 0, and then sort again based on column I, and then delete all columns A to H.

    Wednesday, July 11, 2018 7:01 PM
  • If doing it manually is OK, I would recommend that you copy Column C from above and Paste Special, Text Only into word. That will put each new line and each new cell into a separate line in Word. You can then copy that and paste it back into Excel. Each line in Word will go into a new cell in Excel.


    Ethan Strauss

    Wednesday, July 11, 2018 7:34 PM
  • Hi stillanoob,

    If your requirement is to split the table above to the image as follows, you may refer to the steps below: 


    1. Select the two columns of data source, copy and paste(Keep text only) to Word:

    2. Press Ctrl +H to replace all """ with blank, replace all "," with blank too if necessary.
    3. Copy the data in Word and then paste back to Excel (You may paste to a new sheet.).
    4. Let's say in Step 3 the data is put into column A and B in a new sheet, use the formulas below in column E and F to manually create a new table, remember to drag and drop to fill the rest of the columns:

    E: =IF(ISBLANK(B2),,A2)

    F: =IF(ISBLANK(B2),A2,B2)

    5. Select the data range (E2:F20 in the sample above), press Ctrl+C and then right click in E2, select Values:

    6. Select Column E, replace(Ctrl+H) all 0 with blanks:

    7. Select E2: E10, press F5 or Ctrl+G, click Special > Blanks > OK to locate all blanks cells:

    8. Be sure not to click anywhere else after finishing Step 7, type "=E2" in the formula bar:

    9. Press Ctrl + Enter so that all the other blank cells will be filled with the corresponding part count number:

    10. Input the column name in the first row and then you'll get the table show in the first image.

    Hope the above can be helpful. Feel free to let me know if I have misunderstood anything.


    Yuki Sun

    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Thursday, July 12, 2018 5:59 AM
  • Bernie I believe your method is the closest to achieving what I'm looking to do. For some reason when doing the Text to Columns it doesn't carry over all of the PNs. Some instances have about 5 part numbers in one cell. After doing the Text delimited in cases of 5 part numbers in 1 cell. It only carries over a couple of them and the other 3 disappear.

    My steps

    Select whole column

    Data: Text to Columns: Delimited selected, next, comma and space selected as well as treat consecutive delimiter as one.


    A side note. I just threw the Column A in the picture above for more of a visual. The only true thing I care about at the moment is separating the individual part numbers in column B.

    Maybe I shouldn't have put Col A in the pic above. Perhaps people are getting confused by what I'm looking to do since I included that.

    • Edited by stillanoob Thursday, July 12, 2018 12:08 PM
    Thursday, July 12, 2018 12:05 PM
  • Yuki, thank you for your response I did try your method but I need this to be a bit more automated. Your method works but it a bit more work than I need. Again thank you for your response, I do appreciate it!
    Thursday, July 12, 2018 12:06 PM
  • When you use Text to columns, also use the "Other" box.  Place your cursor in that box, press and hold the Alt key and type 0010 on your number keypad. 

    Some of your entries have 'soft returns' which Text to columns can handle but you need to use the above procedure.

    Thursday, July 12, 2018 1:59 PM
  • Hmm when I hold down the "Alt" key and try and type in 0010 nothing is populating in the box.

    Additionally I tried to type it in without the Alt key held down and it will only allow 1 digit the first " 0 " in 0010

    • Edited by stillanoob Thursday, July 12, 2018 2:17 PM
    Thursday, July 12, 2018 2:12 PM
  • Nothing should appear in that box since you are entering a carriage return character.  You can also use a macro - select a cell in that column and run this macro:

    Sub TestMacro()
        ActiveCell.EntireColumn.Cells.Replace Chr(10), ",", xlPart
    End Sub

    Thursday, July 12, 2018 8:30 PM
  • Hi stillanoob,

    Just checked in to see how everything is going with the issue. If Bernie's suggestion above was helpful, it would be appreciated that you can help mark his post as answer so that others who might have a similar issue can benefit from your thread. Thanks for your understanding and support.


    Yuki Sun

    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Monday, July 16, 2018 9:40 AM