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

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

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

If the first cell is A2, place this formula in B2 and drag it down. HTH
Otto
=Right(A2,7)
"Concord" wrote in message
...
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

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

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!




"Concord" wrote:

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

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

If you always want the last 7 characters:

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



Concord wrote:

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

--

Dave Peterson

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

=RIGHT(A1,7)

Copy down.


Gord Dibben MS Excel MVP

On Thu, 15 Oct 2009 09:28:14 -0700, Concord
wrote:

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