Fixing postal addresses

[Sir Babydum GBE]

Hi - this is your erstwhile VBAXer back in excel business (sort of) and I have a a tricky one (methinks).

I import addresses from a database each day. They come in linear form and i need them to be converted to, er, label-ish type form - looking something like you would see an address look like on a letter.

Problem is that the addresses are often entered in different ways, or wrong. So sometimes there's no space in the postcodes (as far as I know, there are the second part of a postcode is always Space Number letter letter), other time when I'm expecting just a street, i get a street and a town. Counties arse supplied in the same cells as post codes - but we don't need the county on the label. etc.

Attached is an example sheet. Columns A to E contain the database format, Column G contains the properly formatted address - a space is . is it possible to get a macro to sort this?

i can cope at the moment because the customer base is small (around 20 per day). We're expecting that to go into the hundreds soon - and that's a lot of laborious address fixing.

thanks in advance for any help - it's good to be back

Sir BD

[Bob Phillips]

I would create a table of all possible counties, and then just look at the last field upto the first space and see if it matches, if not try upto the second space, and so on.

[Sir Babydum GBE]

I will give that a try - after all, the biggest issue on a Postal address is that the postcode needs to be there with a house number. So I guess the other stuff isn't quite so importand

Thanks

BD