Data Cleaning

Hi everyone,

Could you please provide me steps to clean data in the file attached?

• Seperate data to different row if there are symbol ',': For example, "AA1, BB1, CC1" is in cell A2. I need the "AA1" in cell A2, while "BB1" in the cell A3 and etc.
  
• Remove cell (shift up) if there is no number in the cell: For example, "AA" in the cell A4 should be excluded from the list because it doesn't contain number.

Please take a look at the file attached for my expected output with samples.

Thank you and have a nice day

Short answer, you will need VBA for this.

How did "DD4" end up in the expected results sheet? Also, "EE1" disappeared from the expected results sheet.

Where did EE1 (cell A3) go to?

Also you say removal of AA but you start off with two AA1s and end up with three AA1s.
Where did the extra AA1 come from?

Hi millz and Special-K,

I have revised #1 to clarify my problem.
In addition, I have modified excel file to align with my problem's description.
Thank you and have a nice day

No one understands what you're trying to do.

	A	B	C	D	E	F
1	Input				Output
2	A	B			A	B
3	AA1, BB1, CC1	AA1, BB1, CC1, DD1			AA1	AA1
4	EE1	FF1			BB1	BB1
5	AA				CC1	CC1
6					AA1	DD1
7					EE1	FF1

Where did the AA1 in E6 come from?

Where did the AA in E5 go?

This is assuming the second "AA1" is a mistake. Try running this on the Input sheet.

Please Login or Register  to view this content.