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.

How to put lots of text files into a single excel spreadsheet all in one go? Has spaces between the different. Text to Columns. Group and Outline PivotTable and PivotChart Report. Import External Data Refresh Data Import Data. New Web Query. Microsoft Excel - View Insert Format Tools Data Window Help B Type a question for help U.

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: =INDEX(A:A,MOD((ROW()-2),COUNTA(A:A)-1)+2) Then in J2, enter =INDEX($B:$H,MOD((ROW()-2),COUNTA(A:A)-1)+2,INT((ROW()-2)/(COUNTA(A:A)-1))+1) 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.

Hi stillanoob, If your requirement is to split the table above to the image as follows, you may refer to the steps below: Steps: 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.

Copy the data in Word and then paste back to Excel (You may paste to a new sheet.). Mac os x download iso for windows. 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. Best file format for usb drive between mac and pc windows 10. Be sure not to click anywhere else after finishing Step 7, t ype '=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. Regards, Yuki Sun Please remember to mark the replies as answers if they helped.

If you have feedback for TechNet Subscriber Support, contact. Click to learn more.

Visit the dedicated to share, explore and talk to experts about Microsoft Teams. 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.

Finally: 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. 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. Regards, Yuki Sun Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact. Click to learn more.