Prev: optimum method to print rows as columns
Next: Filter a list for missing numbers
From: מיכאל (מיקי) אבידן on 10 Feb 2010 10:56
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
> <LucasLaumans(a)discussions.microsoft.com> 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
> .
>
From: Ron Rosenfeld on 10 Feb 2010 13:59
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
First  |  Prev  | 
Pages: 1 2
Prev: optimum method to print rows as columns
Next: Filter a list for missing numbers