Text to Columns

[Wonder]

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

 

[Don Guillett]

Post a list of examples before/after

 

[Wonder]

Post a list of examples before/after

--
Don Guillett
Microsoft MVP Excel
SalesAid Software






- 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.

 

[Don Guillett]

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