split post code (zip code) out of cell that includes full address

C

Concord

Excel sheet comprises 1000+ records where each address occupies a single cell
e.g.
21 Select Avenue, London, SW3 5PX
31 Shaftsbury Drive, Stanley Park Estate, Liverpool, LP5 6EW

want to extract post code only into a separate cell.
Effectively want 'parse' the last 7 characters (including space).
Any advice/guidance greatfully received

Concord
 
O

Otto Moehrbach

If the first cell is A2, place this formula in B2 and drag it down. HTH
Otto
=Right(A2,7)
 
T

trip_to_tokyo

In EXCEL 2007:-

1. I have entered your first address in cell A1.

In D2 I have entered:-

=RIGHT(A1,7)

SW3 5PX gets returned.

2. I have entered your 2nd address in cell A3.

In D3 I have entered:-

=RIGHT(A3,7)

LP5 6EW

- gets returned.

If my comments have helped please hit Yes.

Thanks!
 
D

Dave Peterson

If you always want the last 7 characters:

=right(a1,7)
or
=right(trim(a1),7)
(if there may be trailing spaces)
 
Top