Systematically separate text to columns

[igoodable]

Hi all,

I have a very complex challenge. I need to extract semi-unstructured data from a cell into different columns and re-arrange them.

Basically, my data have many thousand rows that looks like this:
DETAILS: 10000# 100 MESH, 150000# 40/80 HP, 50000# 20/40 EP
DETAILS: 162000# 40/80 HP, 50000# 20/40 EP

And I want it ending up like this (each pipe is a column):
10000|100 MESH|15000|40/80 HP|50000|20/40 EP
BLANK|BLANK|162000|40/80 HP|50000|20/40 EP

Please see my sample workbook attached, it's easier to illustrate there.

Thanks in advance

[amy_d2]

select d3 to down further go text to column --- delimited ---in delimiter check tab , semicolon , comma, & other in side box of other type # ---then finish

then select column D press ctrl + H --- type DETAILS:(and one space) & click replace all

then filter all your column chek blank cell in last two coulmns in you data & rearrange them


or to avoid all this direct this question to VBA forum

[igoodable]

Thank you Amy, my challenge is putting all similar items on the same column. For example, there are times that 20/40 EP will comes before 40/80 HP for the same ID. Sorry I forgot to state that in my problem.