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
.