Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Syns
 
Posts: n/a
Default Splitting multiple cell contents containing values at end

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
  #2   Report Post  
Duke Carey
 
Posts: n/a
Default

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

  #3   Report Post  
RagDyeR
 
Posts: n/a
Default

Try this:

=RIGHT(A1,4)


--

HTH,

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

"Syns" wrote in message
...
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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Format based on contents of cell. dave m Excel Worksheet Functions 13 April 29th 05 01:12 PM
How do I find the contents of a cell using the "ADDRESS" function. sweeney Excel Worksheet Functions 2 April 5th 05 03:23 AM
cell contents Kevin Excel Discussion (Misc queries) 1 March 8th 05 12:23 PM
Can I use the contents of a cell to satisfy the result_vector arg. robh_2 Excel Worksheet Functions 3 February 24th 05 09:14 PM
Multiple X-Axis Values Rob Herrmann Charts and Charting in Excel 2 January 23rd 05 11:57 PM


All times are GMT +1. The time now is 10:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"