Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format based on contents of cell. | Excel Worksheet Functions | |||
How do I find the contents of a cell using the "ADDRESS" function. | Excel Worksheet Functions | |||
cell contents | Excel Discussion (Misc queries) | |||
Can I use the contents of a cell to satisfy the result_vector arg. | Excel Worksheet Functions | |||
Multiple X-Axis Values | Charts and Charting in Excel |