Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Location
    Clearwater, Florida, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Labels to txt (Office 2K)

    I work with messed up mailing lists all day. If something can't be used by our list guys, It gets sent to me. I can turn almost anything into a usable csv or txt as long as it has some common denominator from which to build. Often I get lists from programs like, my mail list, Avery, dbf's and all sorts of stuff and untill now can always fix it. The problem I am having should be simple to fix, I just know it, but I can't seem to come up with the answer...so here it is:
    The file is a pdf that looks like a label sheet. Some of the addresses are 3 lines tall, some 4, and some 5. I have made it as far as making it into a txt file that is one column, top to bottom, with a blank line between each address. Normally I do my VB work in Word and write a simple macro that brings the lower line up, adds a tab, brings the next one up...etc. This works great if they are all the same number of lines...But right now I have 2 customers that gave me these lists that have 3 then 5 then 4 then 4 then 3 etc... I can't come up with anything to fix it. I tried a program called "ListGrabber", but it isn't as accurate as I would like. These lists are up to 20,000 records so manual labor is out of the question... ANY IDEAS ???

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Labels to txt (Office 2K)

    Am I to understand that your only outstanding problem is to handle variable lines between blank lines?
    If so, it is still a fairly trivial Word excercise <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Turn ^p^p into something recognisable with a global replace - e.g. XYZZY
    Turn ^p into tabs with a global replace
    Turn XYZZY back into ^p

    It should then be done

  3. #3
    New Lounger
    Join Date
    Jul 2002
    Location
    Clearwater, Florida, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Labels to txt (Office 2K)

    Andrew Ollivier is a god.

  4. #4
    New Lounger
    Join Date
    Aug 2002
    Location
    Issaquah, Washington, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Labels to txt (Office 2K)

    I have hundreds of addresses (3 or 4 lines) which are formatted in label format (up to 30 per page, 3 columns, 10 rows) in multiple Word documents. I have saved one document in .txt format and then copied it into Excel. I have hopes of converting the whole collection into an Excel list. Is this the best idea? Would your macros help me? Do you ever have to split data such as City, State, Zip into separate fields? Is there a really available macro to do that kind of thing?

  5. #5
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Labels to txt (Office 2K)

    Ross

    I'd consider doing the task initially in Word.
    Word is somewhat better at dealing with characters (and doesn't translate numbers into other things).
    The initial task appears to be to
    turn the 3 x 10 format back into a 'list'

    That is fairly easy but depends a bit on the format of the 3 columns - what are they separated with? (e.g. tabs, blanks, or a table)

    Once you have a list (or sets of lists) the rest becomes more predictable to handle.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Labels to txt (Office 2K)

    See if This Post will get you started.
    Legare Coleman

  7. #7
    Super Moderator
    Join Date
    Dec 2000
    Location
    Renton, Washington, USA
    Posts
    12,560
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Labels to txt (Office 2K)

    Ross and others,
    I am making a guess that these labels are done in a table format of some sort.

    One way is to find and replace ALL "paragraph markers" with a different string, I use ????. Then I converted the tables to text, with a paragraph marker as the text separator. This made each table cell a separate line. Then replaced my ???? with tabs. Saved this as text file, then opened this text file in Excel, and is now a spread sheet.

    I know that you VBA experts can take the steps and come up with a macro that will do most of this for one.

    Now running HP Pavilion a6528p, with Win7 64 Bit OS.

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Labels to txt (Office 2K)

    I don't think we have a smilie for that <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    New Lounger
    Join Date
    Jul 2002
    Location
    Clearwater, Florida, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Labels to txt (Office 2K)

    <P ID="edit" class=small>(Edited by WebGenii on 21-Nov-02 11:52. to add hyperlink)</P>Ross,
    Sorry it took me so long to reply. I am off on Fri & Sat. The answer to all your questions is Yes, and as Andrew suggests, I do almost all my work in Word. I often recieve lists with the City, St. & zip in one field, and normally open the list in Excel, copy the C, S, Zip collumn into a new sheet, then save as a tab delimeted (don't use csv because the fields will often have comma's in them that are needed). Then I do a simple "find & replace" to get everything how I want it. Example: find ", FL " (meaning: commaspaceFLspace) and replace with " FL " (meaning: tabFLtab). Then save as txt (tabs) and open in a new sheet in excell, add 3 collumns in the original sheet, copy and paste the new data into the new collumns, and compare a few rows at the top and a few at the end of the document to the old data to make sure everything stayed aligned, then delete the old collumn. There may be easier ways to do it, but in my business there is NO margin for error and this way (plus never working from the original file, always a copy) gives me that precision. Here is a link to the macro's for proper font case: http://support.microsoft.com/default.aspx?...b;EN-US;Q213649 and if you have alot of addresses together that are not ALL 3 or 4 lines, Andrew's handling worked well, but afterwards you will still have to put them into an Excel sheet, sort by length and copy/paste into a new sheet, creating empty fields in the ones that are shorter.

    Got it? Let me know if you need more help.
    Christian
    mis@postcardmania.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •