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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,218,497
Messages
6,142,821
Members
450,449
Latest member
Dave Carr QM

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
Back
Top