Converting some text to an Excel Document...

  • I have a Word document that more or less looks the same as the following:


    N012326144|3809939|GONZALES|RAMON|||860-826-5189||155 MARKET ST||NEW BRITAIN|CT|06051|624|39.99|M|2160397||2002-03-04 19:53:55| N012326116|3806263|DIOT|MATTHEW|||203-380-2051||115 KNOWHON ST||STRATFORD|CT|06615|744|34.99|M|2160397||2002-03-04 19:41:12| N010714977|3786629|HORTON, JR|RANDOL|||661-871-9353||1318 PESANTE RD||BAKERSFIELD|CA|93306|674|39.99|M|2160387|A|2002-03-04 19:21:25| N010715665|3803399|||AGS STAINLESS|GARY GIFFIN|206-842-9492||7873 NE DAY RD||BAINBRIDGE ISLAND|WA|98110|705|29.99|M|2159888||2002-03-04 19:02:47| N012326134|3806820|FAULKNER|BRYUAN|||781-605-1060||27 BLAINE ST||MALDEN|MA|02148|677|34.99|M|2160397||2002-03-04 19:01:38| N012321878|3804713|BUTCHER|LETA||LETA BUTCHER|520-579-5656||3450 W MONTGOMERY ST||TUCSON|AZ|85742|761|29.99|M|2159799||2002-03-04 18:56:19| N013069240|3807573|SCOTT|GARFIELD|||305-653-1615||1370 NW 191 ST||MIAMI|FL|33169|700|33.99|M|2149567||2002-03-04 18:55:53|


    It apparently WAS a spreadsheet of some kind at one point, but somehow got messed up and I need to convert it back. Each new entry is denominated by the N######### unique number at the beginning. The file is about 5.5mb and 1800 pages long. It APPEARS to me that the " | " character is being used to separate the cells, and wherever values were not available, you see back to back, or sometimes even three " | " characters. My FIRST thought was to replace all of the " | "'s with " , "'s to make a CSV (Comma Separated Value) file and go from there... but I'm lost after that.

    The problem I was having w/ the CSV thing was that there is no RETURN character at the end of each entry, before the next N######### value, so when you put it all directly into a spreadsheet, it places it all on one line...

    Any suggestions? Or any better approaches? Thanks in advance for your help...!

  • Hi mateo1221 and welcome,

    Try this,
    Save the word file as a plain text file, Text1.txt.
    From Excel run this macro.

    Now try opening the new text file in excel using | as the field separator.



  • Turn Word's show/hide button on.
    Select all the text up to and including the paragraph return that follows it.
    Hit Table-Convert, Text to Table.
    Type the pipe | character into the "Separate text at" box. Tell it you want 19 columns. Hit Ok.
    This created a perfect table when I did it using the text you pasted in here.
    Copy and paste right into Excel.


    Save the file as TXT and use Excel's text import wizard to open it. This MAY not work as well.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!