Extract Street Names from Addresses

I have a column (Excel 2007) which contains 20,000 addresses. I need to extract the "street / road name" part of the address and place it in a separate column.
Each address consists of a number of parts - generally 5 to 7, and each part is separated from the next by a comma
Example 1 (5 parts)
12, Any Road, Nottingham City, Nottingham, NG5 7HY

Example 2 (6 Parts)
Appt 3, 45, Any Street, Nottingham City, Nottingham, NG8 2JY

Example 3 (7 Parts)
Flat 18, High House, 107, Any Drive, Nottingham City, Nottingham, NG1 9FT

Whilst the number of parts prior to that which I wish to extract varies there are always 3 parts after the part I wish to extract and those 3 parts are always 38 characters in total. I'm sure this must be possible using a combination of the Text functions but to date I havent found the right combination. Text to Columns won't work because of the differing number of parts preceeding the part I want. Does anyon have any ideas / suggestions? Thanks for you time!

Originally Posted by makaza

I have a column (Excel 2007) which contains 20,000 addresses. I need to extract the "street / road name" part of the address and place it in a separate column.
Each address consists of a number of parts - generally 5 to 7, and each part is separated from the next by a comma
Example 1 (5 parts)
12, Any Road, Nottingham City, Nottingham, NG5 7HY

Example 2 (6 Parts)
Appt 3, 45, Any Street, Nottingham City, Nottingham, NG8 2JY

Example 3 (7 Parts)
Flat 18, High House, 107, Any Drive, Nottingham City, Nottingham, NG1 9FT

Whilst the number of parts prior to that which I wish to extract varies there are always 3 parts after the part I wish to extract and those 3 parts are always 38 characters in total. I'm sure this must be possible using a combination of the Text functions but to date I havent found the right combination. Text to Columns won't work because of the differing number of parts preceeding the part I want. Does anyon have any ideas / suggestions? Thanks for you time!

Hi,

you could try something like:

text to columns, comma separated
in Z1 put

=COUNTA(A1:Y1)

in AA1 put

=A1&IF(Z1>4,", "&B1&IF(Z1>5,", "&C1&IF(Z1>6,","&D1&IF(Z1>7,", "&E1&IF(Z1>8,", "&G1,""),""),""),""),"")

and formula fill both down to the extent of your data.

hth
---

Bryan, I ran your formulas and it doesn't seem to do what makaza was looking for, and - if I'm correct - that is to extract JUST the address (road/street) section in bold.

Your formula extracts the first section if the # of "words" is 5, the first two sections if the # of "words" is 6, and the first three sections if the # of "words" is 7.

If just the street part is being sought, try this:

Column A = all of your info delimited by ","

Perform Data--> Text to Columns. For the delimiter, choose Comma. Click OK, then Finish.

Your data should then be in columns B:F, B:G or B:H depending on if there are 5, 6 or 7 words. (Up until now, this is the same as Brian's instructions)

In I1 (and filling down), type: =COUNTA(B1:H1)
This will return 5, 6 or 7. (Adjust formula as necessary, in case you use different columns)

In J1 (and filling down), type: =TRIM(IF(I1=5,D1,IF(I1=6,E1,F1)))
This will pull just the 'any address' part and put it in column J. (Again, adjust formula as needed if you used different columns.)