Split multiple Delimiter field with Header into Multiple Columns and Rows

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.

Try

Please Login or Register  to view this content.

Try this:-
Results sheet2

Please Login or Register  to view this content.

Regards Mick

WOW Perfect ... you are a super star

It works great.

I am quite novice in VB, do you mind explaining the code so i can have a better understanding on how you did it. It may help others who try to do the same thing.

Thank you so very much.
Omer.