Hi Guys,
I'm having some real issues trying to seperate street names out of cells that contain other characters.
I've tried Trim, Len, Substitute, Left, Right, Lookup, Mid, Search and Index within formulas and still can't seperate the data like i need to. different formulas have been working for some of the addresses, but i can't seem to find a way to make it work for the entire data source.
I have 5000 rows of addresses, and need to take the street and unit numbers out of the cell so i can sort by soley street names.
As you can see below the other issue is that in front of some of the addresses it can have the words Ground, Floor, Level or G/F. Trying to seperate numbers, -, /, A's, B's and words like Ground Floor from the front of the address seems impossible....can anyone help me out with a formula that can do it accurately?
Here is a sample of the data i'm working with in each cell -
1 Molesworth Street
1/112 Gilbert Street
1/132-136 Gray Street
58A Tynte Street
Floor 1 14/7 Liberman Close
Ground 4/23 Wellington Square
Level 2 2/12 Cromwell Street
Level 7-8 24/261 Pirie Street
Ground 8D/17 Eden Street
Ground GD/100 South Terrace
GF-1F 1/105 Carrington Street
GF-MF 3/61-69 Brougham Place
Floor 5-6 51/42 Charlick Circuit
253B Gilles Street
24 Little Sturt Street
293 Gilbert Street
This is what i'm needing the result of the formula to look like from the above data set -
Molesworth Street
Gilbert Street
Gray Street
Tynte Street
Liberman Close
Wellington Square
Cromwell Street
Pirie Street
Eden Street
South Terrace
Carrington Street
Brougham Place
Charlick Circuit
Gilles Street
Little Sturt Street
Gilbert Street
Any help would be extremely appreciated.
Thanks!
-Further to the above, the below formulas being used together has worked for the majority of the addresses.....but comes unstuck once there is a word like level or ground floor at the start of the address.
=LEFT(D2,FIND(" ",D2,1))
=TRIM(RIGHT(D2,(LEN(D2)-LEN(G2)+1)))
Bookmarks