Extracting parts of a cell

annegrey

New Member
Joined
Nov 17, 2005
Messages
32
Hi

I have a list of text as follows:

Port Melbourne Vic 3207
Clifton Hill Vic 3068
Melbourne Vic 3001
South Melbourne Vic 3205
Lorne Vic 3232
Lorne Vic 3232

I want to separate the City, State and Postcode into 3 columns. Any help will be appreciated.

Thanks
AG :p
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

riaz

Well-known Member
Joined
Jun 27, 2006
Messages
779
Hi Anne

It appears that your zip code is always the last 4 characters, and your state is the next 3 before that. If that is the case

=LEFT(A1,LEN(A1)-9) gives you Port Melbourne
=MID(A1,LEN(A1)-7,3) gives you Vic
=RIGHT(A1,4) gives you 3207

Just watch there are no trailing spaces in your data after the zip code. If there are, just adjust the numbers in the formulae by one and you should be ok.

If your states and zip codes are not the same length all the time, let me know and we'll work something else out.

Hope this helps.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,424
Office Version
  1. 365
Platform
  1. Windows
Anne

If they are all Australian addresses and the State is always 2 or three characters long, then try this:

B2: =TRIM(SUBSTITUTE(A2,C2&" "&D2,""))
C2: =TRIM(LEFT(RIGHT(A2,8),4))
D2: =RIGHT(A2,4)
All copied down.
Mr Excel.xls
ABCDE
1Full AddressCityStatePostcode
2Port Melbourne Vic 3207Port MelbourneVic3207
3Clifton Hill Vic 3068Clifton HillVic3068
4Melbourne Vic 3001MelbourneVic3001
5South Melbourne Vic 3205South MelbourneVic3205
6Lorne Vic 3232LorneVic3232
7Lorne Vic 3232LorneVic3232
8Adelaide SA 5000AdelaideSA5000
9
Split Addresses
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,
Book1
ABCD
1Port Melbourne Vic 3207Port MelbourneVic3207
2Clifton Hill Vic 3068Clifton HillVic3068
3Melbourne Vic 3001MelbourneVic3001
4South Melbourne Vic 3205South MelbourneVic3205
5Lorne Vic 3232LorneVic3232
6Lorne Vic 3232LorneVic3232
Sheet1


Formula in B1,

=TRIM(SUBSTITUTE(A1,C1&" "&D1,""))

In C1,

=REPLACE(TRIM(SUBSTITUTE(A1,D1,"")),1,LOOKUP(9.9999999E+307,FIND(" ",TRIM(SUBSTITUTE(A1,D1,"")),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))),"")

In D1,

=REPLACE(A1,1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&1234567890))-1,"")

HTH
 

Forum statistics

Threads
1,136,353
Messages
5,675,285
Members
419,559
Latest member
BraytonM

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
Top