Splitting Cells and Merging Cells within the same Row

[juleskaynel]

Hi there, I am looking for a solution, maybe someone can help.

We have several lines of data and right now everything is in the same row. As you can see in one of the cells, there are multiple part #'s in the same cell. Because of this, I can't do a vlookup to add the status of each of these part #'s in another cell. I can manually split this out and then merge the rows before and after this cell. However, that's a lengthy process. Any suggestions on how I can do that quickly?

 

[kweaver]

It would help if you (a) changed your profile to indicate what version of Excel you are running and (b) post your sample data using XL2BB.

If you're running 365, have you tried something like this? Is that you want to happen?

Book5
	J	K	L	M	N	O
2	1234-55-6 543-1234-18V 67890-100-200		1234-55-6	543-1234-18V	67890-100-200
3	10111-2960 78901-7777TPRE 12345-2000-ALS 1016-2008		10111-2960	78901-7777TPRE	12345-2000-ALS	1016-2008
Sheet1

Cell Formulas
Range	Formula
L2:N2,L3:O3	L2	=TEXTSPLIT(J2,CHAR(10))
Dynamic array formulas.

 

[Anonymous1378]

What would your desired output look like? I'd know how to deal with a single column, but if you want the other columns to behave in any other way besides repeating themselves in every row, I'd need examples of your desired output.

 

[juleskaynel]

This is my desired outcome. I have hundreds of lines to do this to, which is why I'm looking for a quicker way to do it.

See how I created separate line items for the information that was preiously in one cell and then I merged all the other cells?

 

[alansidman]

Cannot manipulate data in a picture. Please use XL2BB to upload sample data or use third party such as dropbox.com or box.net to upload data.