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

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

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.

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

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

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
.

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