seperate street name and number

[Guest]

I have street addresses in 1 column - I want to seperate into 2 columns the
first being the number and the second being the street name. I'm using the
function MID but I have to keep changing when amount of numbers difer. Must
be a better way.
Thanks

 

[Guest]

Try: Data > Text to Columns

 

[Guest]

With
A1 containing a street address.....eg: 123 Main Street

Try something like this:
B1: =LEFT(A1,FIND(" ",A1)-1)
C1: =MID(A1,FIND(" ",A1)+1,255)

In the above example,
B1 returns "123"
C1: returns "Main Street"

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP