Sorting Addresses in Excel 4

[Crystalamateur]

Hi there
I need to provide an excel spreadsheet of all our addresses for a mailing. The mailing house has requested that the columns are cleaned up as follows:-
Column 1: Name
Column 2: First line of address
Column 3: Second line of address
Column 4: Third line of address
Column 5: Post Town
Column 6: Postcode

We have addresses that range between 3 and 5 lines.
Is there any way in Excel that I can move all the Post Towns & postcodes into their correct columns.
The mailing house has advised that their labeling system ignores and blank columns when printing the address.

Many thanks

 

[xlbo]

kinda depends on you having something that identifies post towns and postcodes...

what is the format of your data ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Crystalamateur]

Hi Geoff

The format of my data:
Post towns are in capitals
Pots Codes (UK) are a combination of capital letters and numbers eg AL12 6PP or S1 5WW

Does this help

Thanks
Peter

 

[GlennUK]

If you currently have addresses that range between 3 and 5 lines, are these in a list somehow? Are there separators between entries? Are you talking about multiple lines in a single cell maybe? I think that is what Geoff was asking ... what is the format of your data as a whole?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Crystalamateur]

Hi Glenn

Sorry about the confusion.
I have exported the data from Crystal Reports into an Excel spreadsheet.
Each row of the spreadsheet has 7 columns
Column 1: Ref No.
Column 2: Name
Column 3: address line 1
column 4: address line 2
column 5: address line 3
column 6: address line 4
column 7: address line 5

Most addresses are 4 lines so they have data up to and including column 6. Others only have 3 lines of an address and their data goes up to column 5. Then we have other addresses that have 5 lines so their data goes up to column 7.

Is this as clear as mud?

Thanks
Peter

 

[KenWright]

Dummy the data and post an example is what they are trying to get at. It is so hard trying to interpret what someone else sees simply from a description, when an example makes everything clear in an instant.

Dummy up about 3 or 4 records and just post those.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

Glenn - thanks yes - that was what I meant

What is important here is that there is some logical way of defining which columns hold the Post Town and Postcode data - and yes - I am well aware of what a postcode looks like thanks ;-)

Can we, for instance, assume that the last 2 columns of data in the address will always be the post town and postcode - if so, then this is an easy task...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bac998]

Hi,

I had to do a similar operation last year.

If your list is VERY clean - i.e. you know 100% that the last two entries of every row are the postal town and postcode - then a fairly simple macro could do this for you.

If you are not 100% confident of the list then the best bet is to do it manually:

Sort by columns 6,5,4
find the blanks in column 6 and move the postcodes across (they should be fairly nicely grouped where they occur in columns 5 and 4).
Then do the same sorting by columns 6,3
Then do the same for postal towns, sort by 5,4,3 and move towns.

Sounds painful but it's not that bad

Do remember to save a copy before you start, and save regularly while you are doing the work, good luck.

Bruce

 

[Crystalamateur]

Hi Bruce

Many thanks for your tip, it works!

Cheers
Peter