Separating Post (Zip) Codes from Full Address

[JamesKM]

I have been given a list of addresses in Excel, with the full address including the post code all in one cell. How would I separate the post code. I attach an example of some addresses (all made up) to show that the address length can change as can the post code length. Discussing with a colleague we thought that if a formula could look into the cell from the right to the first comma then take what's right of there and trim.
Any ideas appreciated.

******** ******************** ************************************************************************>

Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A1 =
	A	B	C	D
1	Address*in*Full	Address*Part	Post*Code	*
2	22*Heath*Drive,*London,*N3*3DW	*	*	*
3	17*Falmouth*Road,*Falmouth,*North*Devon,*TN11*7PJ	*	*	*
4	Flat*6,*Chester*Court,*Regents*Road,*Burnley,*BN12*1BF	*	*	*
5	4*The*Road,*Knightsbridge,*London,*EC1W*4XX	*	*	*
Sheet1 *

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

[Iridium]

Try the text to columns function (Data¦Text to columns)

Chris

[Richie(UK)]

Hi James,

Others will, no doubt, help you with an Excel formula based solution. In the meantime, have a look at this function from David McRitchie's site. (You could swap the / in the example for a ,).

Return string after last "/" character

Posted on or before 2000-02-19, though still missing (D.McRitchie)
=AFTLAST(E22,"/")

Function AFTLAST(cell As Range, findchar As String) As String
Application.Calculation = xlCalculationManual 'in XL97
For i = Len(cell) To 1 Step -1
If Mid(cell, i, 1) = findchar Then
AFTLAST = Mid(cell, i + 1, 99)
Exit Function
End If
Next i
AFTLAST = cell ' or aftlast="" depending on what you want
Application.Calculation = xlCalculationAutomatic 'in XL97
End Function

HTH

[JamesKM]

Chris,
Thanks for the quick response, but this would give the post code in different columns each time (see the example to note that address lengths and comma separated parts as well as post code lengths can vary).

If possible could anyone suggest a macro or formula that for the next two columns:
1. Deletes the post code only (and last comma) part and
2. Reproduces the post cofde only part.

[DonkeyOte]

IGNORE...sorry!

This should get the post code out

=MID(A1,VALUE(FIND(",",A1))+VALUE(FIND(",",MID(A1,FIND(",",A1)+1,20)))+1,8)

_________________
LASW10

[ This Message was edited by: lasw10 on 2002-10-17 10:59 ]

[JamesKM]

Richie, Brilliant Thanks.

I have also modified it to produce a before last one if anyone wants it:

Function BEFLAST(cell As Range, findchar As String) As String
Application.Calculation = xlCalculationManual 'in XL97
For i = Len(cell) To 1 Step -1
If Mid(cell, i, 1) = findchar Then
BEFLAST = Mid(cell, 1, i - 1)
Exit Function
End If
Next i
BEFLAST = cell ' or beflast="" depending on what you want
Application.Calculation = xlCalculationAutomatic 'in XL97
End Function

_________________
Thanks in Advance,

James

[ This Message was edited by: JamesKM on 2002-10-17 11:01 ]

[Jack in the UK]

Hi James

You work BELFAST eh!! Ok these a better way BT is Northern Irelands Post Code all teh same all over NI, my wonderful great friends all over Belfast EVERY day tellme this and laught at my South London tone[s]

I do not understand your OP aske postcode now you using town, what if you have Omar, Larne, Downpatrick, Naere, Roundlestown and all the rest???? BT will not fail.

Il have a edit onthe code, but direct ne more you need

1 the high street BELFAST
BT39 2DF

Yes

Jack


Do you also have the Republic to do the same with.

[Jack in the UK]

Hi

OK Northern Ireland has nothing what so ever to do with this - im going mad, but as i do deal with NI i have functions to do this thus teh questions and detailed knowledge - BAD day job change to Analyst ho hum!

[JamesKM]

Thanks for all the help so far. I was wondering if I could take this thread one step further. Namely:
I can show the part after the last comma, the part before the last comma and the part before the FIRST comma, but how can I get the section between the first and last commas. EG if I had the address:
Big Shop, 5 The Road, Knightsbridge, London, EC1W 4XX
How can I reproduce: "5 The Road, Knightsbridge, London"?

(This would mean from an address string I could pull off three columns: Company Name, Address, Post Code.

My fiddling with previous functions given has been unsuccseful, but I have the following to work from:

Public Function AFTFIRST(cell As Range) As String
Application.Calculation = xlCalculationManual 'in XL97
For j = 1 To Len(cell) Step 1
If Mid(cell, j, 1) = "," Then
AFTFIRST = Mid(cell, j + 1, 99)
Exit Function
End If
Next j
AFTFIRST = cell ' or aftlast="" depending on what you want
Application.Calculation = xlCalculationAutomatic 'in XL97
End Function

Public Function BEFLAST(cell As Range) As String
Application.Calculation = xlCalculationManual 'in XL97
For i = Len(cell) To 1 Step -1
If Mid(cell, i, 1) = "," Then
BEFLAST = Mid(cell, 1, i - 1)
Exit Function
End If
Next i
BEFLAST = cell
Application.Calculation = xlCalculationAutomatic 'in XL97
End Function


Public Function BEFFIRST(cell As Range) As String
Application.Calculation = xlCalculationManual 'in XL97
For i = 1 To Len(cell) Step 1
If Mid(cell, i, 1) = "," Then
BEFFIRST = Mid(cell, 1, i - 1)
Exit Function
End If
Next i
BEFFIRST = cell
Application.Calculation = xlCalculationAutomatic 'in XL97
End Function

[Richie(UK)]

Hi James,

There is a function on the J-Walk site that should help you out. Take a look here:

http://j-walk.com/ss/excel/tips/tip32.htm

HTH