Constructing an Excel Table from Text File Using VBA Code | VBA & Macros | Excel Forum

I am new to VBA and therefore would like your assistance in constructing VBA code that can hopefully ease my work load.

I need assistance with writing VBA code that can extract information from a text file and arrange it into an Excel table in the form of records.
One record in the text file can occupy two to four or even five lines. But each record, begins with an alphanumeric number and ends with a fullstop (period) “.”. So a period will denote the end of a record and the next line begins a new record.

I would like the table to have the following columns:

1. Estate Number – this is an alphanumeric field that begins at the beginning of the text until immediately before the dash “—”.
2. Surname – the surname begins after “(2)” (without quotes) and ends immediately before the first comma (“,”).
3. Christian Names – these are found between the first comma and the second comma and may comprise 1, 2 or even 3 names.
4. DoB – this will come after the second comma and will end with the 3rd comma.
5. ID Number – the ID number will comprise 13-digit number that will be between the 3rd comma and the fourth comma.
6. Address – the address will comprise several lines after the 4th comma and will end with the first semi-colon.
7. Date of Death – this will begin soon after “(3)” (without quotes) and will end with second semi-colon
The following fields may not be found in all records depending whether one was survived by a spouse or not.

8. Spouse’ Name – The spouse’s name will start immediately after the “(4)” without the quotes. If the (4) does not appear in a record then these fields must have “N/A”. The spouse’s name will end with a comma (note that both surname and first names have not been separated) 
9. Spouse’s DoB - After the Spouse’s name comes the date of birth.
10. Spouse’s ID Number – The spouse ID will be a 13-digit field that comes after the DoB, separated by a comma. 
The data will be paste on sheet 2 and I want the table to be on sheet 1.

Sample Data
017611/2017—(2) BACALHAU, JOSE SILVESTRE, 19 March 1951, 5103195109084, 36 SUMMERVIEW ESTATES 1A
FISHEAGLE DRIVE, THREE RIVERS, VEREENIGING; (3) 20 May 2017; (5) LIEBENBERG MALAN LIEZEL HORN INC, 20 UECKERMANN STREET, HEIDELBERG,1441.
010444/2017—(2) HEYLEN, BRANDON, 4 October 1983, 8310045018087, 6 ARABIER CRESENT, GREENHILLS EXT5,
RANDFONTEIN; (3) 21 April 2017; (4) N/A N/A; (5) Truter Crous Wiggill Inc, Corner of Convent and Kenneth road, Nashet Building, Greehills, Randfontein; (6) 30.
25811/2013—(2) MOSE, HELLEN MMABATHO, 29 January 1981, 8101295297087, 11468 VIOLET CRESCENT
MOHLAKENG, RANDFONTEIN; (3) 16 January 2013; (4) N/A N/A; (5) Truter Crous Wiggill Inc, Corner of Convent and Kenneth road, Nashet Building, Greehills, Randfontein; (6) 30.
015471/2017—(2) MONGOEGI, THAPELO IVAN, 5 March 1953, 5310305617084, 2305 PYNAPPEL STREET,
TOEKOMSRUS; (3) 2 July 2016; (4) MARTHA NOMVULA MONGOEGI, 1 May 1957, 5705010576089; (5) Truter Crous Wiggill
Inc, Corner of Convent and Kenneth road, Nashet Building, Greehills, Randfontein; (6) 30.
014543/2017—(2) BASSON, CHRISTIAN DE JONGH, 22 September 1927, 2709225022080, 49 JAN FISKAAL STREET,
UNIT 9 KRASNEY, EIKEPARK, RANDFONTEIN; (3) 6 May 2017; (4) MARTHA JOHANNA BASSON, 24 September 1931,
3109240022083; (5) Truter Crous Wiggill Inc, Corner of Convent and Kenneth road, Nashet Building, Greehills, Randfontein; (6) 30.
2983/2017—(2) BODENSTEIN, ANNA DOROTHEA, 4 Februarie 1931, 3102040026081, GARSFONTEIN PRETORIA;
(3) 14 Oktober 2016; (4) N/A N/A; (5) FOSTER PROKUREUR, OTTOSTRAAT 12 OTTOSDAL 2610.
16935/2017—(2) Netshaula, Matakuvhona, 25 April 1958, 5804255660086, 407 Isithame Section, Tembisa, 1632;
(3) 24 May 2017; (4) Azwindini Sarah Ndhlovu, 24 June 1962, 6206240311081; (5) Nkuna Attorneys, Summore Centre, 3rd Floor, Office No:. 2, 348 Rev RTJ Namane Drive, Corner Columbia Street, Tlamatlama Section, Tembisa; (6) 30 days.
010967/2017—(2) DOORSAMY, NITHAYANATHAN, 10 March 1969, 6903105131089, UNIT 18 INDIANAPOLIS, 167
ALBERTYN STREET, KYALAMI HILLS, EXT 4, 1684; (3) 24 March 2017; (5) TANNERS & ASSOCIATES C/O BERNARD TANNER, 10 ANN CRESCENT (CNR. LINDEN ROAD) SANDOWN, SANDTON.
3465/2017—(2) MANOTA, SERAME JOEL, 25 May 1961, 6105255743081, ERF 271, MASOGANENG SECTION,
MAMETLHAKE; (3) 6 February 2017; (4) MITTA KETSE MANOTA, 18 November 1964, 6411189791081; (5) DAVID EDWARD
WILLIAMS, 16 SUTTER ROAD, BELA-BELA, 0480.
018059/2017—(2) MOKOENA, MOTLALEPULA ORIEL, 30 Oktober 1953, 5310305650085, 1331 MPANZA STREET,
RATANDA, HEIDELBERG; (3) 20 Mei 2016; (4) MOLELEKENG EVELYN MOKOENA, 20 Januarie 1961, 6101200679082;
(5) LIEBENBERG MALAN LIEZEL HORN INC, 20 UECKERMANN STREET, HEIDELBERG,1441.
014569/2017—(2) Daitz, Sylvia, 20 July 1924, 2407200016082, Old Age Home C321, Sandringham Gardens, Johannesburg; (3) 15 November 2010; (5) Legatus Trust, 263 Kent Anvenue, Randburg.
16515/2017—(2) Kala, Kasan, 17 June 1934, 3406175053087, 12 Lily Avenue, Ext
(3) 6 June 2000; (4) N/A; (5) Bharatkumar Kasan and Jayant Kasan Kala, 12 Lily Avenue, Ext 3, Lenasia, Johannesburg. 18391/2017—(2) Kroutz, Marie Shirley, 8 November 1930, 3011080081080, 31 Skelton Road, Mid-Ennerdale,
Johannesburg .; (3) 24 June 2017; (4) N/A; (5) Peter Christopher Jacob Kroutz, 31 Skelton Road, Mid-Ennerdale, Johannesburg.
010291/2017—(2) Johnson, Gerty Anna, 11 Junie 1933, 3306110015086, 30E Laan 426 Villieria Pretoria; (3) 28 Mei 2017;
(5) Joubert Scholtz Ingelyf as Agent vir Absa Trust Bpk, 11 Heideweg, Kempton Park, 1620.
018037/2017—(2) BOTHA, JAN DE KLERK, 14 Augustus 1974, 7408145127088, ANDANTE 7, NARSINGSTRAAT,
BRACKENHURST, ALBERTON, GAUTENG; (3) 21 Julie 2017; (5) TJ BOTHASPANGENBERG ING, POSBUS 393,
ALBERTON, 1450; (6) 30 DAE.
010064/2017—(2) Vosloo, Hermanus Jacobus, 17 Januarie 1931, 3101175010084, 10 Sunview Mansions Williamsstraat
Wilkoppies Klerksdorp Noordwes; (3) 26 Junie 2017; (4) Fransina Barendina Vosloo, 9 November 1934, 3411090008083;
(5) Estelle Purdy & Magdalena Catharina van der Westhuizen, 6 Monicalaan Wilkoppies Klerksdorp Noordwes. 16935/2017—(2) Netshaula, Matakuvhona, 25 April 1958, 5804255660086, 407 Isithame Section, Tembisa, 1632;
(3) 24 May 2017; (4) Azwindini Sarah Ndhlovu, 24 June 1962, 6206240311081; (5) Nkuna Attorneys, Summore Centre, 3rd Floor, Office No:. 2, 348 Rev RTJ Namane Drive, Corner Columbia Street, Tlamatlama Section, Tembisa; (6) 30 days.
16935/2017—(2) Netshaula, Matakuvhona, 25 April 1958, 5804255660086, 407 Isithame Section, Tembisa, 1632;
(3) 24 May 2017; (4) Azwindini Sarah Ndhlovu, 24 June 1962, 6206240311081; (5) Nkuna Attorneys, Summore Centre, 3rd Floor, Office No:. 2, 348 Rev RTJ Namane Drive, Corner Columbia Street, Tlamatlama Section, Tembisa; (6) 30 days.