SquillyFish
New Member
- Joined
- Sep 3, 2014
- Messages
- 1
Hello clever excel people,
Please can you help me. I have pulled data from a website that needs to be sorted and cleaned then loaded into our shiny brand new website.
The address part of the data is a nightmare.
So far I have managed to split PO Boxes from physical addresses, but now I am stuck.
This is the sort of layout I am trying to split into
PO Box *** |Suburb | CITY | postal code
The current data looks like this:
"PO Box 90948
Victoria Street, West
Auckland, 1142"
"PO Box 1565
Queenstown"
"PO Box 11169
Ellerslie
Auckland, 1542"
PO Box 87550
Meadowbank
Auckland, 1743
As you can see, some will use all 4 columns, some will not. There are 'some' commas, and some spaces, and some tabs but a lot that are not spaced at all. A lot are written like this : PO Box1234AucklandWellesleySt, Auckland1010
Thank you in advance for any help.
Kind Regards
Squilly
Please can you help me. I have pulled data from a website that needs to be sorted and cleaned then loaded into our shiny brand new website.
The address part of the data is a nightmare.
So far I have managed to split PO Boxes from physical addresses, but now I am stuck.
This is the sort of layout I am trying to split into
PO Box *** |Suburb | CITY | postal code
The current data looks like this:
"PO Box 90948
Victoria Street, West
Auckland, 1142"
"PO Box 1565
Queenstown"
"PO Box 11169
Ellerslie
Auckland, 1542"
PO Box 87550
Meadowbank
Auckland, 1743
As you can see, some will use all 4 columns, some will not. There are 'some' commas, and some spaces, and some tabs but a lot that are not spaced at all. A lot are written like this : PO Box1234AucklandWellesleySt, Auckland1010
Thank you in advance for any help.
Kind Regards
Squilly
