Assuming data in col E, formulae
to get the number in f1
=LEFT(E1,FIND(" ",E1))
to get the rest in g1
=RIGHT(E1,LEN(E1)-FIND(" ",E1))

or a macro to do it for all and leave just the values
Sub splitaddress()
For Each c In Range("e1:e6")
c.Offset(, 1) = Left(c, InStr(c, " "))
c.Offset(, 2) = Right(c, Len(c) - InStr(c, " "))
Next
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Wonder" wrote in message
oups.com...
On Jul 20, 10:59 am, "Don Guillett" wrote:
Post a list of examples before/after

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Wonder" wrote in
message

ups.com...



I have a list of addresses that I need to separate the street number
from the street name and unit numbers/floor number. Because there
isn't a common deliminator, how can I do this?

Thanks- Hide quoted text -

- Show quoted text -


Here are some examples of the addresses:

30 ADELAIDE ST., EAST, 11TH FL
400 UNIVERSITY AVE, 21ST FLOOR .
2700 - 14TH AVENUE
895 DON MILLS RD. 1 MORNEAU SOBECO CENTRE, SUITE 600
101 TORO ROAD
2201 EGLINTON AVE E., (PDS)


I need to put just the street number in 1 column and the rest of the
info in another column.