Extracting parts of a cell

[annegrey]

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

 

[SteveO59L]

Use Text to Columns, with a space as the delimiter

 

[riaz]

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]

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
	A	B	C	D	E
1	Full Address	City	State	Postcode
2	Port Melbourne Vic 3207	Port Melbourne	Vic	3207
3	Clifton Hill Vic 3068	Clifton Hill	Vic	3068
4	Melbourne Vic 3001	Melbourne	Vic	3001
5	South Melbourne Vic 3205	South Melbourne	Vic	3205
6	Lorne Vic 3232	Lorne	Vic	3232
7	Lorne Vic 3232	Lorne	Vic	3232
8	Adelaide SA 5000	Adelaide	SA	5000
9
Split Addresses

 

[Krishnakumar]

Hi,

Book1
	A	B	C	D
1	Port Melbourne Vic 3207	Port Melbourne	Vic	3207
2	Clifton Hill Vic 3068	Clifton Hill	Vic	3068
3	Melbourne Vic 3001	Melbourne	Vic	3001
4	South Melbourne Vic 3205	South Melbourne	Vic	3205
5	Lorne Vic 3232	Lorne	Vic	3232
6	Lorne Vic 3232	Lorne	Vic	3232
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