Specific Address Formatting (UK Addresses)

GS90

New Member
Joined
May 23, 2022
Messages
1
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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:


2Flat 236 Fake StreetLondonSE11SE
Flat221 Fake StreetLondonSE11SE
1 Fake StreetLondonSE11SE

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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top