My boss keeps a long (very long) list of business contacts in word format... it is highly disorganized: not all the entries are similarly formatted, various types of supplementary information appears sporadically, and the physical spacing and formatting is odd because he is constantly adding and deleting spaces to make correctly-sized labels for file folders.



Anyway, it's a nightmare. My goal is to get this data in a more useable format AND to get it into our Outlook contacts database, but I'm not about to lift 1,400 pages of contact information and manually convert it to a format that plays well with Outlook.

The question, then, is this: does Word have the capability to sift through this stuff and isolate the address, telephone, and other information and drop it into a spreadsheet? Is there a program or add-in that can help automate this project? Otherwise I fear it will never get done.

Thanks for any help you might have.

MS

Ok, while your waiting for someone to come up with a programme or addin answer these are the steps that I would take if tackled with your issue.

I'm assuming that you have probably 21 labels on a sheet, or hopefully it's on lever arch labels which are far less! If not and the assumption is right that is 29,400 different entries. I can see why you have dread.

First thing I would do would be to save the document as a different document so you still have the original.

Secondly I would clear all the formatting on the Home Tab, Font Group then Clear Formatting (looks like a little eraser), so everything is just your normal font unformatted.

Then I would, on the keyboard press Ctrl F, which brings up the navigation box and choose from there Find and Replace.

Assuming your boss has used spaces for alignment, I'd probably start with by putting 10 spaces in the find dialog section to and 1 space in the replace section and repeat this with a reducing amount, i.e. 9 spaces to 1, 8 spaces to 1. Has your boss used punctuation? If so I'd be inclined to remove this by the find and replace method so there is no punctuation in the document whatsoever. If there are blank lines within each cell I would also use Find and Replace to get rid of extra paragraphs (blank lines), say ^p^p (paragraph mark code) to ^p to get rid of blank lines, repeat this again if you think there might be more blank lines in the labels.

Once you have done this I would then save the document with a different name so you can "go back".

Assuming there is no header rows in these tables (which is what a label document is). Clicking within the table should show you Table Tools on the Ribbon, choices are Design and Layout, Choose Layout, under the Data Group choose Convert to Text, I’d choose Other and use | (Shift+ \), then Find and Replace to replace the paragraph marks (shift 6 and p - ^p) to commas so each “label” should be look something like this:

Word, word, word, word, word, word, word, word|

I would then Find and Replace the | mark with a paragraph mark ^p and save the document.
Select all the text in the document (Ctrl+A), copy (Ctrl+C) and in a blank Excel Document paste it (Ctrl+P) – starting at cell A2.

In Excel, on Data Tab, choose Data Tools group and choose Text to Columns using the Delimited option of a comma.

In row A add your headings. Insert a few columns for your “various types of supplementary information appears sporadically” Unfortunately you will have to cut and paste the sporadic information to the correct places. Save the spreadsheet.

Under the Data Tab there are filter and sort options. Using these it might make your mammoth task seem a little less daunting, by doing it in sections.

How your boss found anything information is surprising!!

Good luck

You could unload your original document and I can have a look for you as it is always easier to see what you are trying to achieve seeing the raw data. If you wish I will get it to the Excel Stage

Julie Willis

Julie -

Wow, thanks for the help! I had already find-replaced a lot of the odd formatting, but you made some great suggestions here that will definitely help make this a manageable task.

And as much as I'd like to let you have a look at the sheet so you can get a clearer picture of what I'm talking about, this is not something I am legally permitted to share. Plus I'd hate to make you do any more work than you already have.

Thank you very much.

MS

No problem, completely understand. Come back if you have any more issues.

Julie Willis