Hi;

I have a multiple columns containing rows of concatenated columns with header delimited seperate by Colon and Field by Tilde.

Some of the Columns does not have any data due to Employee departure from the company.

RowID LocationID Employee 1 Employee 2 Employee 3
1 123456 First Name :John~Last Name :Smith~DOB :01-Jan-1990~Gender :Male~Salary Range :15-21 First Name :Robert~Last Name :Great~DOB :25-Jun-1967~Gender :Male~Salary Range :75-90 First Name :Adele~Last Name :Rose~DOB :18-Sep-1987~Gender :Male~Salary Range :25-30
2 123457 First Name :Angelica~Last Name :Johnson~DOB :11-Feb-1978~Gender :Female~Salary Range :40-50 First Name :Oliver~Last Name :Anderson~DOB :06-May-1970~Gender :Male~Salary Range :40-50


What i need is a VBA Macro that will Split the records into columns with Headers and rows.

RowID LocationID EmployeeID First Name Last Name DOB Gender Salary Range
1 123456 123456-001 John Smith 01-Jan-1990 Male 15-21
2 123456 123456-002 Robert Great 25-Jun-1967 Male 75-90
3 123456 123456-003 Adele Rose 18-Sep-1987 Male 25-30
4 123457 123457-001 Angelica Johnson 11-Feb-1978 Female 40-50
5 123457 123457-002 Oliver Anderson 06-May-1970 Male 40-50


Any help will be much appreciated.

Thanks
Omer.