Parcing Street Addresses

[Gary Kaye]

Need to parce a string of addresses along the samples below to extract the 'house number' the 'directions' of "SW", "N.W.", etc, and the Street Name, i.e. "84th Terrace":

Examples:

12345 SW 84th Terrace
345-B E Lake Road
1245 N.W. 11th Drive
11 Highland Road

Column 1 = House number
Column 2 = SW (Omit if absent, with or without periods included)
Column 3 = Street Name

Can't get a formula to work on every variable!!??

[anno]

you could use text to columns (data menu) using a space as the delimiter but from your samples you'd need to do some concatenation afterwards to put the street number and name back together again - =CONCATENATE(C1,D1) or =C1&" "&D1. also will be a bit untidy in cases where the direction is omitted. a start though.

[bergy]

On 2002-05-16 15:32, Gary Kaye wrote:
Need to parce a string of addresses along the samples below to extract the 'house number' the 'directions' of "SW", "N.W.", etc, and the Street Name, i.e. "84th Terrace":

Examples:

12345 SW 84th Terrace
345-B E Lake Road
1245 N.W. 11th Drive
11 Highland Road

Column 1 = House number
Column 2 = SW (Omit if absent, with or without periods included)
Column 3 = Street Name

Can't get a formula to work on every variable!!??

It's not perfect, but have you tried selecting the cell and then do a Data, Text to Columns, and check Space? Like I said, it's not perfect and it will take some manipulation of some cells, but it is quick and easy.