Specific Address Formatting (UK Addresses)

[GS90]

Hi all!

New to the forum so apologies for anything I've missed when putting this together. I think this is a fairly straightforward query but I'm just having difficulty simplifying or automating this, if its really possible.

We use a system for some verification and it now requires addresses in this specific format:
Flat Number
House Name
House Number
Street
Town
Post Code

The problem I'm having is whichever genius created our office system decided free text wouldn't be a problem there, and the addresses also export in a different format. Our addresses export in this format:
Building/Building name
Building Number
Street
Town
Post Code

Currently I have to do this task every day and usually the best way is to do some typical RIGHT/LEFT formulas to extract the building/flat number and then the address etc. Unfortunately the Building Number tends to house the Flat Number and the Street will have the actual house number in its cell almost all of the time.

So often data will look like this:

2	Flat 2	36 Fake Street	London	SE11SE
Flat	2	21 Fake Street	London	SE11SE
		1 Fake Street	London	SE11SE

I hope this makes sense, its a bit difficult to show from my actual work perspective but the point is the data format the company wants, and the data export we do, is quite different. Unfortunately the company is very strict (part of a public sector agency) so we don't really have any additional Excel tools that might make this a bit easier.

Is there possibly anything recommended outside of the usual RIGHT/LEFT formulas, or perhaps even a VBA query, that would be able to effectively 'translate' these? Again I can live with it if there's not really a better alternative but its very tedious!

Any help would be greatly appreciated.

 

[pjmorris]

I would investigate PowerQuery - its a part of Excel rather than an add-in so you should have it. You might have to do a bit of learning, but it will be worthwhile as once setup correctly the transformation will happen whenever you refresh the data.
Remember though that addresses often don't have numbers in at all!
And kick the individual who decided free text was OK!