• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Constructing an Excel Table from Text File Using VBA Code|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Constructing an Excel Table from Text File Using VBA Code|VBA & Macros|Excel Forum|My Online Training Hub

sale now on

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosConstructing an Excel Table from Te…
sp_PrintTopic sp_TopicIcon
Constructing an Excel Table from Text File Using VBA Code
Avatar
Luckson Chirwa

New Member
Members
Level 0
Forum Posts: 1
Member Since:
September 18, 2017
sp_UserOfflineSmall Offline
1
September 18, 2017 - 7:37 pm
sp_Permalink sp_Print

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.

Avatar
YasserKhalil
Member
Members
Level 0
Forum Posts: 13
Member Since:
June 30, 2016
sp_UserOfflineSmall Offline
2
September 19, 2017 - 2:39 am
sp_Permalink sp_Print

Cross-Post at these links

https://www.excelforum.com/exc.....-code.html

http://error-creating-relation.....-vba-code/

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 170
Currently Online: Alan Sidman, Velouria
Guest(s) 87
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 858
Velouria: 580
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 214
A.Maurizio: 202
Aye Mu: 201
Jessica Stewart: 189
Newest Members:
Vincent Starcevich
Patrick Lawrence
Louise Bell
GIJO GEORGE
Kumud Patel
Atos Franzon
Andrew MacDonald
Artie Ball
Jenn Cain
Peter Vandeweg
Forum Stats:
Groups: 3
Forums: 24
Topics: 6079
Posts: 26684

 

Member Stats:
Guest Posters: 49
Members: 31570
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.