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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

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
55,853
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,172,166
Messages
5,879,428
Members
433,429
Latest member
Ever

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