Splitting multiple cell contents containing values at end

[Syns]

I am working on an excell sheet and have over 6000 adresses in one column.At
the end of each row in this column, the postal code of the area. Each cell is
merged.
I need to split the postal code from the adress in each row, but to to it
row by row will take me forever.

Is there any way which I can use to take out only the values at the end of a
single row or select the last 4 digits of the whole column at once.
EXAMPLE:

P O BOX 151 MOSSELBAY 6500 Split to:PO Box 151 Mosselbay
6500
48 KARATARA CATHRINE LA MONTAGNE 0184 Split to:................
0184
P.O. BOX 16195 ATLASVILLE 1465 Split to:...............................
1465

6500 of these adresses continues in the same format.

Please help!
Syndy

[Duke Carey]

If postal code is ALWAYS and ONLY the last four characters:

=LEFT(address,LEN(address)-5)
gives you the address, & it assumes there's one space before the postal code

=RIGHT(address,4)
gives you the postal code


"Syns" wrote:

> I am working on an excell sheet and have over 6000 adresses in one column.At
> the end of each row in this column, the postal code of the area. Each cell is
> merged.
> I need to split the postal code from the adress in each row, but to to it
> row by row will take me forever.
>
> Is there any way which I can use to take out only the values at the end of a
> single row or select the last 4 digits of the whole column at once.
> EXAMPLE:
>
> P O BOX 151 MOSSELBAY 6500 Split to:PO Box 151 Mosselbay
> 6500
> 48 KARATARA CATHRINE LA MONTAGNE 0184 Split to:................
> 0184
> P.O. BOX 16195 ATLASVILLE 1465 Split to:...............................
> 1465
>
> 6500 of these adresses continues in the same format.
>
> Please help!
> Syndy

[RagDyeR]

Try this:

=RIGHT(A1,4)


--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Syns" <Syns@discussions.microsoft.com> wrote in message
news:1BF928DD-140A-47D7-A42C-78CCCE14B332@microsoft.com...
I am working on an excell sheet and have over 6000 adresses in one column.At
the end of each row in this column, the postal code of the area. Each cell
is
merged.
I need to split the postal code from the adress in each row, but to to it
row by row will take me forever.

Is there any way which I can use to take out only the values at the end of a
single row or select the last 4 digits of the whole column at once.
EXAMPLE:

P O BOX 151 MOSSELBAY 6500 Split to:PO Box 151 Mosselbay
6500
48 KARATARA CATHRINE LA MONTAGNE 0184 Split to:................
0184
P.O. BOX 16195 ATLASVILLE 1465 Split to:...............................
1465

6500 of these adresses continues in the same format.

Please help!
Syndy