Excel - converting address labels to columns

[elgeejay]

Here are examples of 3 and 4 line addresses in a Word file of labels that I have imported into Excel. Now, I need to convert the 3 and 4 line addresses to columns in Excel; e.g., col headings like:

Salutation/Company/Street/City/State/Zip (some with 5 digit Zip, some with Zip+4).

There is one blank row between each address.

Jane, Kathy, & Gladys
Burke Blinds
4924 Lyngate Ct.
Burke, VA 22015

Mr. & Mrs. Harley Dorffman
3388 Chesma Drive
Woodbyne, VA 22192-4333

Can anyone provide something that can do this conversion? Thanks!

 

[VulcanJedi]

I'm confused if these labels are from Word...don't you have the original datasource used to create the labels? Wouldn't it be easier to deal w/ this source data?

Tranpkp

 

[elgeejay]

Thanks for the quick reply.

Yes, I wish I could. But the person who created the list typed them one-by-one into a Word label template from a handwritten list. I sure don't want to have to cut/paste everything.

 

[VulcanJedi]

i don't think this is that bad except for the first line which would require massive amounts of string manipulation / logic to parse that.
Are the 1st lines remotely standardized?
IE are the 3 line blocks alwasy 'personal' while the 4 lines are Companies?
IE therefore you need only to parse the first line for 3 line blocks for salutation?
Do any of the addresses have middle initials?

Otherwise I'd suggest parsing the first line from the end for spaces....

Tranpkp

 

[cheerio]

This is non-trivial.
If I had this problem I would work on a copy of the original Word document.

First, do a global search and replace
Search for ^p (this is a paragraph marker)
Replace with some character not already in the document as a separator: I will assume ~

Second do a global search and replace
Search for ~~ (if that was your separator)
Replace with ^p

After this each address should be on a line of its own.

Now for the boring bit.
Do a global search for , VA
Replace with ~VA~
Repeat for all the other 2 letter state codes present.

Now import the data into Excel
Split the text on each line into columns using the text to columns wizard and using the ~ separator to identify the columns.

Now sort on the right hand column.
For the shorter addresses this will be blank.
Now you have all the shorter addresses together move the address, apart from the addressee name, one column to the right so that column B is blank - these should be the addresses without a company name. You can do this by inserting cells into column B and opting to move remaining cells right.

Now look at the data and see what problems remain.

 

[SkipVought]

Hi,

"Can anyone provide something that can do this conversion?"

No.

As cherio stated, this is not trivial. Depending on the length of the list, it could take an experieced person days and you will STILL have errors. Experienced in...

parsing techniques
data analysis
data maqnipulation

How many addresses are in this list?

Skip,

_[red][/red]

 

[VulcanJedi]

I NEVER said this would be trivial.
But if there is a systemic way this was still manually entered it may not be as bad. Parsing through the address / city/state/zip doesnt' require anything really significant? I stated that the first line would require considerable amount of code(which may not even be worth it), but the balance of the address label block shouldn't require nearly as much(relatively)

Tranpkp

 

[xlhelp]

Seems to me that doing the following might work.

Slight variation on what cheerio has suggested.

In Microsoft Word:

Under Edit, Replace command in the Find What box type ^p

In the Replace with box type a special character such as $

Replace all.

Again,
Find what: $$
Replace with ^p

Again,
Find what: $
Replace with: , (comma)

Save file with CSV extension.

Open in Excel.

Member- AAAA Association Against Acronym Abusers

 

[CBasicAsslember]

How long is the list? It seems that it would be easier to get the formatting right in the Word document first. As a variation on the Find/Replace:

1) Go to edit -> Replace
2) In the “Find What:” box type in a comma followed by a space.
3) Put your cursor in the “Replace With” box and press the “special” button.
4) Select “Manual Line Break”.

Cycle through all the commas and replace the commas with line breaks so that you get this.

Jane, Kathy, & Gladys
Burke Blinds
4924 Lyngate Ct.
Burke
VA
22015

Mr. & Mrs. Harley Dorffman
3388 Chesma Drive
Woodbyne
VA
22192-4333

Go through all the addresses and add a blank line so that they all have the same number of rows:

Jane, Kathy, & Gladys
Burke Blinds
4924 Lyngate Ct.
Burke
VA
22015

Mr. & Mrs. Harley Dorffman

3388 Chesma Drive
Woodbyne
VA
22192-4333

Copy and paste all the addresses into Excel starting a A2. Put your lables, Salutation/Company/Street/City/State/Zip, in cells B1 to G1. Select B2 to G2 and type in the formula:

=Transpose(A2:A7)

DO NOT HIT ENTER, press ctrl + Shift + enter to create an array formula. The equation should look like this:

{=Transpose(A2:A7)}

Select cells B2 to G2 and copy. Paste the formula in the same row that the 'Salutation' is in for each record.

Last, copy all the cells in columns B threw G and paste special 'Values' into a different sheet. To eliminate all the spaces do a sort (Data --> Sort).

 

[elgeejay]

Thanks for all the input! I thot perhaps this was something that others regularly encountered and that there might be a more or less "standard" solution. Clearly that is not the case.

The mailing list contains about 1,000 addresses, so doing it manually would be quite time-intensive.

I will try using the method(s) suggested and, again, I truly appreciate the assistance.

If anyone else has suggestions I'm still open to those as well.

 

[CBasicAsslember]

Post it in the Office VBA forum. What I posted here could be done in VBA but would require a bit more detail.

 

[bluedragon2]

One thing that worked for me:

In Word, replace manual line breaks with ;
Then replace ;; with manual line break
Then copy into excel
Then do text to columns with ; being the delimiter

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...