How 2 separate column of addresses into a colum of names and numb

Hi!

I would like advice on how to separate the names and the numbers of an
address written in one column into the name and the number in two separate
columns.
In otherwords "Kingstreet 23" must become "Kingstreet" and "23."

Can anyone help with a function that can do this?

Thanks in advance,

Rochelle

Hi Rochelle

if all your names & numbers are similar to your example, then data / text
to columns will probably work for you

ensure that you have a couple of blank columns to the right of your current
column (note, this process will split up your original column into two and
won't retain a copy of the original - if you want a "combined" column, copy
this column to a new column first).

select the column to be split up, choose data / text to columns, choose
deliminated choose NEXT, untick TAB, tick SPACE, click FINISH.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Rochelle" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> I would like advice on how to separate the names and the numbers of an
> address written in one column into the name and the number in two separate
> columns.
> In otherwords "Kingstreet 23" must become "Kingstreet" and "23."
>
> Can anyone help with a function that can do this?
>
> Thanks in advance,
>
> Rochelle

One way:

In place (e.g., A1 -> A1:B1):
Choose Data/Text to Columns. Click "Delimited". Click Next. Check the
Space checkbox, click Finish.

By formula:

A1: Kingstreet 23
B1: =LEFT(A1,FIND(" ",A1)-1)
C1: =MID(A1, FIND(" ",A1)+1, 255)

In article <[email protected]>,
"Rochelle" <[email protected]> wrote:

> Hi!
>
> I would like advice on how to separate the names and the numbers of an
> address written in one column into the name and the number in two separate
> columns.
> In otherwords "Kingstreet 23" must become "Kingstreet" and "23."
>
> Can anyone help with a function that can do this?
>
> Thanks in advance,
>
> Rochelle

Thanks to both of you!!

Rochelle

"JE McGimpsey" wrote:

> One way:
>
> In place (e.g., A1 -> A1:B1):
> Choose Data/Text to Columns. Click "Delimited". Click Next. Check the
> Space checkbox, click Finish.
>
> By formula:
>
> A1: Kingstreet 23
> B1: =LEFT(A1,FIND(" ",A1)-1)
> C1: =MID(A1, FIND(" ",A1)+1, 255)
>
> In article <[email protected]>,
> "Rochelle" <[email protected]> wrote:
>
> > Hi!
> >
> > I would like advice on how to separate the names and the numbers of an
> > address written in one column into the name and the number in two separate
> > columns.
> > In otherwords "Kingstreet 23" must become "Kingstreet" and "23."
> >
> > Can anyone help with a function that can do this?
> >
> > Thanks in advance,
> >
> > Rochelle
>