Hello all,
I am trying to parse the city out of an address, but the data i receive is not very stable, an example of the 10,000 rows is below.
891 Mt Eden Road, , Auckland,
9 york road, haruru falls, paihia, 0200
Beach Road, mangonui, mangonui, 0494
Paramount Plaza, Tikipunga, Whangarei, 0112
26 Ti Rakau Drive, Botany, Auckland, NZ
26 Ti Rakau Drive, Botany, Auckland, NZ
State Highway1 Road4, Cape Reinga, Far North,
62 Glenmall Road, Glen Eden, Auckland, New Zealand
68 Moore Street, Northcote, Auckland, NZ
68 Moore Street, Northcote, Auckland, NZ
178 ALBANY HIGHWAY, ALBANY, AKLD,
178 ALBANY HIGHWAY, ALBANY, AKLD,
CNR LYNN & BAYVIEW RD, GLENFIELD, AKLD,
11 ELIZABETH STREET, , WARKWORTH, NZ
Corner of Clyde & Anzac Roads, , Browns Bay,
208 COATESVILLE RIVERHEAD HIGHWAY, , Coatsville,
208 COATESVILLE RIVERHEAD HIGHWAY, , Coatsville,
6 CORONATION ROAD, GLENFIELD, AUCKLAND,
135 East Coast Road, Crown Hill, Auckland,
57 Forge Road, , Silverdale,
156 Birkdale Road, North Shore, Auckland, New Zealand
23 Chartwell Avenue, Glenfield, Auckland,
I have a list of all the cities that may come through and i would like to have a forumla that identifies the city in the column next to it. I can think of ways to start with VBA but i would really like to have this done with a formula as others will be using it too.
i have tried combinations of match, find, mid, but i cnt for the life of me work out what to do never mind how to do it!
any suggestions of what route to try next?
Thankyou!
Bookmarks