Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Seperate text and numbers in a column?
Hi,
could anyone help me out with the following: I've got a column which contains addresses (e.g. A1 = mountainroad 17 , A2 = mainstreet 168a , etc.) In order to work with this data I will need to seperate the streetnames from the housenumbers. Would anyone have a suggestion on how to do this? As there are many lines, it takes quite some time to do this manually. KR, Lucas |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Seperate text and numbers in a column?
Hi
If all your addresses have the same format as your two examples, ie text-space-number, you can use the Text to Columns feature. Select the addresses and in the toolbar click on Data. Go to Text to Columns... and in step1 of the wizard make sure Delimited is checked. Then in step 2 check Space as the delimiter and Finish. NB I am using Excel 2003. "Lucas Laumans" wrote: Hi, could anyone help me out with the following: I've got a column which contains addresses (e.g. A1 = mountainroad 17 , A2 = mainstreet 168a , etc.) In order to work with this data I will need to seperate the streetnames from the housenumbers. Would anyone have a suggestion on how to do this? As there are many lines, it takes quite some time to do this manually. KR, Lucas |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Seperate text and numbers in a column?
Split on the first space:
B1=LEFT(A1,SEARCH(" ",A1)-1) C1=MID(A1,SEARCH(" ",A1)+1,999) Phil Hibbs. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Seperate text and numbers in a column?
Hi PJ,
thanks, but unfortunately this is not the case. The street names could also consists out of 2,3 or even more words. KR, Lucas "PJ" wrote: Hi If all your addresses have the same format as your two examples, ie text-space-number, you can use the Text to Columns feature. Select the addresses and in the toolbar click on Data. Go to Text to Columns... and in step1 of the wizard make sure Delimited is checked. Then in step 2 check Space as the delimiter and Finish. NB I am using Excel 2003. "Lucas Laumans" wrote: Hi, could anyone help me out with the following: I've got a column which contains addresses (e.g. A1 = mountainroad 17 , A2 = mainstreet 168a , etc.) In order to work with this data I will need to seperate the streetnames from the housenumbers. Would anyone have a suggestion on how to do this? As there are many lines, it takes quite some time to do this manually. KR, Lucas |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Seperate text and numbers in a column?
On Wed, 10 Feb 2010 02:34:01 -0800, Lucas Laumans
wrote: Hi, could anyone help me out with the following: I've got a column which contains addresses (e.g. A1 = mountainroad 17 , A2 = mainstreet 168a , etc.) In order to work with this data I will need to seperate the streetnames from the housenumbers. Would anyone have a suggestion on how to do this? As there are many lines, it takes quite some time to do this manually. KR, Lucas From your example, it appears as if you want to split off the word after the Last space. e.g. B1: =LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ", CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) C1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Seperate text and numbers in a column?
Wouldn't this - in B1 - be simpler and shorter ?
=SUBSTITUTE(A1,C1," ") Micky "Ron Rosenfeld" wrote: On Wed, 10 Feb 2010 02:34:01 -0800, Lucas Laumans wrote: Hi, could anyone help me out with the following: I've got a column which contains addresses (e.g. A1 = mountainroad 17 , A2 = mainstreet 168a , etc.) In order to work with this data I will need to seperate the streetnames from the housenumbers. Would anyone have a suggestion on how to do this? As there are many lines, it takes quite some time to do this manually. KR, Lucas From your example, it appears as if you want to split off the word after the Last space. e.g. B1: =LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ", CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) C1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) --ron . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Seperate text and numbers in a column?
On Wed, 10 Feb 2010 07:56:01 -0800, ????? (????) ????? <micky-a*at*tapuz.co.il
wrote: Wouldn't this - in B1 - be simpler and shorter ? =SUBSTITUTE(A1,C1," ") Micky Simpler, shorter, but not as robust. In the OP's example, the address number is always at the end of the string. Your solution fails if the address number happens to be included in the street name. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seperate Text and numbers in Post code | Excel Worksheet Functions | |||
How can I seperate numbers from text (in one cell) into 2 cells? | Excel Discussion (Misc queries) | |||
seperate first 2 lines of column in seperate columns in same row | Excel Discussion (Misc queries) | |||
place text seperated by a space in a column into seperate columns | Excel Worksheet Functions | |||
how do i seperate data from one column into two seperate ones in . | Excel Discussion (Misc queries) |