How to split a single line text file to multiple lines and export to excel with 2005

Posted on 2011-10-27
Last Modified: 2012-05-12
I am trying to read a file on the c: drive that has 1 single line of text. I need to split the line into multiple lines and exprot them into a excel spread sheet with each line being an individual record. I would like to use 2005 to do this because the original file will contain about 20,000 records. Listed below is a sample of the text in the file. I have placed x's where confidential information should go. The single line of text has multiple spaces in it and the original file had 4 lines per record. I was able to concantanate all the lines to gether and now I don't know how to split them as a individual line and have each line as a record in excel.

In my string the lines are separated by the tab character. There is also some text at the beginning of each record that can be used as a separator as well. (MATCH TYPE)

example of string:

MATCH TYPE: N   SUB NAME: xxxxxxxxxxxxxxxxxx    SSN CODE: V SSN:  xxx-xx-xxxx  MPI #: xxxxxxxxx PART: NP FIPS: 111 TRAN STATE: 21   RET NAME: xxxxxxxxxxxxxxxxxxx    MULTI SSN:             WORKER: 015C002  CLAIM TYPE: DISABILITY         RESP DATE: 10/14/2011        ADDL NAMES:                                                                                                                         SOCIAL SECURITY           xxxxxxxxxxxxxxxxxxxxxxxxx       BOWLING GREEN KY 42104                                                                                                                                                                                           MATCH TYPE: N   SUB NAME: xxxxxxxxxx          SSN CODE: V SSN:  xxx-xx-xxxxxxx      MPI #: xxxxxxxxxxx  PART: NP FIPS: 067 TRAN STATE: 21   RET NAME: xxxxxxxxxx          MULTI SSN:             WORKER: 034C035  CLAIM TYPE: AUXILIARY          RESP DATE: 10/14/2011        ADDL NAMES:                                                                                                                         SOCIAL SECURITY           xxxxxxxxxxxxxxxx DR         LEXINGTON KY xxxxxxxxxxxxx                                                                                                                                                                                           MATCH TYPE: N   SUB NAME: xxxxxxxxxxxxxxxx        SSN CODE: V SSN:  xxx-xx-xxxx  MPI #: xxxxxxxxx PART: NP FIPS: 047 TRAN STATE: 21   RET NAME: xxxxxxxxxxxx        MULTI SSN:             WORKER:          CLAIM TYPE: DISABILITY         RESP DATE: 10/14/2011        ADDL NAMES:                                                                                                                         SOCIAL SECURITY           xxxxxxxxxxxxxxxx             MADISON TN 371159906                                                                                                                                                                                            

Question by:samiam41
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Please post a sample input file, and a sample Excel file showing what the output should look like based on that input.
    LVL 9

    Accepted Solution

    I have resolved this issue myself. I first import the file into a text file and then used to replace the *'s with vbtab, from there I was able to separate each record and upload to excel and set each column where I was able to pull out unwanted columns and save the columns I needed. I then uploaded that data to an acess database and created a report from there. Thanks
    LVL 9

    Author Comment

    I've requested that this question be closed as follows:

    Accepted answer: 0 points for samiam41's comment http:/Q_27418898.html#37071292
    Assisted answer: 500 points for matthewspatrick's comment http:/Q_27418898.html#37041764

    for the following reason:

    LVL 9

    Author Comment

    I choose the wrong button I ment to chose accept as solution
    LVL 9

    Author Closing Comment

    I figured out how to do what I needed by taking a few more steps then I had thought about before.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Title # Comments Views Activity
    how to link excel with word 6 29
    Vlookup for IP 3 33
    Sending email from Excel 2016 VBA and Outlook 2016 4 33
    Employees list 3 30
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
    Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now