Address Parsing

[bpiereder]

I though about using a complicated title like "Extract Phrase Z after Phrase X and Y that may also appear before Phrase Y" but that is just confusing.

I have nearly 5000 data entries of addresses. Many different types of addresses as they are all businesses. Very inconsistent data to work with. No commas to work with. Examples:
Could be as simple as: 123 MAIN STREET
Or as complicated as: 45-123 KINGS BAY RD SW BAY F
Which might be alternatively written as: BAY F SUITE 45 123 KINGS BAY ROAD SW
Or: 123 KINGS BAY ROAD SW BAY F SUITE 45

The Unit/Suite number is sometimes just a number separated from the street number by a space or hyphen, or at the end or somewhere in the middle of the string immediately following the word UNIT, SUITE, or STE
There are also addresses in French which can often have STE in the street name, eg: RUE STE CATHERINE
There are also rural addresses like NW QUARTER RR18-W3
Note: the word "BAY" can indicate an area on a large warehouse (which might be numbered or lettered), can be a part of a street name (like BAY ST or BAYVIEW AVE), or can be a type of street (like WOOD VALLEY BAY)
There is a trailing space on every address (not sure if this could be useful)

I need to parse the string into different columns. I need: Unit/Suite/Ste#, Bay#, Street#, Street Name, Street Type, Street Direction. Type and Direction would be fine to include in the Street Name.

Any help is appreciated.

Here are some formulas I have already (the addresses are in column C):
Bay# parsing:
=IFERROR(IF(AND(ISNUMBER(--MID($C1,SEARCH("BAY ?",$C1)+4,1)),MID($C1,SEARCH("BAY ?",$C1)+4,1)<>"S",MID($C1,SEARCH("BAY ?",$C1)+4,1)<>"A",MID($C1,SEARCH("BAY ?",$C1)+4,1)<>"R",MID($C1,SEARCH("BAY ?",$C1)+4,1)<>"V",MID($C1,SEARCH("BAY ?",$C1)+4,1)<>"H"),MID($C1,SEARCH("BAY ?",$C1)+4,(SEARCH(" ",$C1,SEARCH("BAY ?",$C1)+4))-(SEARCH("BAY ?",$C1)+4)),""),"")

[bpiereder]

Ok, these are the formulas that are working so far:
Bay#:
=IFERROR(IF(AND(MID($C1,SEARCH("BAY ?",$C1)+4,1)<>"S",MID($C1,SEARCH("BAY ?",$C1)+4,1)<>"A",MID($C1,SEARCH("BAY ?",$C1)+4,1)<>"R",MID($C1,SEARCH("BAY ?",$C1)+4,1)<>"V",MID($C1,SEARCH("BAY ?",$C1)+4,1)<>"H"),MID($C1,SEARCH("BAY ?",$C1)+4,(SEARCH(" ",$C1,SEARCH("BAY ?",$C1)+4))-(SEARCH("BAY ?",$C1)+4)),""),"")
Suite#:
=IFERROR(MID($C3,SEARCH("SUITE ?",$C3)+6,(SEARCH(" ",$C3,SEARCH("SUITE ?",$C3)+6))-(SEARCH("SUITE ?",$C3)+6)),"")
Unit#:
=IFERROR(MID($C1,SEARCH("UNIT ?",$C1)+5,(SEARCH(" ",$C1,SEARCH("UNIT ?",$C1)+5))-(SEARCH("UNIT ?",$C1)+5)),"")

I'm working on a formula to pull the STE where it stands for SUITE (not Saint in french). then we can easily combine SUITE, STE, and UNIT into one column (since it doesn't make a difference what we call it).

[bpiereder]

This seems to work for STE (ignoring where the STE stands for Saint in French):
=IFERROR(IF(AND(ISNUMBER(--MID($C3,SEARCH("STE ?",$C3)+4,1)),MID($C3,SEARCH("STE ?",$C3)+4,1)<>"S",MID($C3,SEARCH("STE ?",$C3)+4,1)<>"A",MID($C3,SEARCH("STE ?",$C3)+4,1)<>"R",MID($C3,SEARCH("STE ?",$C3)+4,1)<>"V",MID($C3,SEARCH("STE ?",$C3)+4,1)<>"H"),MID($C3,SEARCH("STE ?",$C3)+4,(SEARCH(" ",$C3,SEARCH("STE ?",$C3)+4))-(SEARCH("STE ?",$C3)+4)),""),"")

I'm just trying to figure out how to pull the unit numbers that are not indicated by a word and instead in any of the below formats. I'm not sure that it's possible (or at least perhaps not possible with my skill level):
B45-123 KING ST
45A 123 KING ST
45-123 67 AVE
45 123 67 AVE
17-19 123 KING ST
17 19 123 KING ST
17-19 123 67 AVE
17 19 123 67 AVE

[hoyasaxa215]

What are the unit numbers in your example? B45/45A/45 etc.?

[bpiereder]

Yes those are the unit numbers.

[hoyasaxa215]

If the unit numbers are never more than 3 characters, as it seems from your examples, then you could use the formula below (assuming addresses are in A1):

Please Login or Register  to view this content.

[bpiereder]

Yeah the unit number can be more than three. My main roadblock is that the unit number can consist of two number numbers separated by a hyphen separated from the street number by a hyphen. This could indicate a range of unit numbers (like 17-19-200 ABC ST where 17-19 is the unit number), or a single two-part unit number (2105-12-354 ABC ST). But of course, it's not always a two-part unit number.

Further to that, there are many rural addresses that barely follow a format. For instance NW QUARTER 540-2-W3, or SE 5-43-23-W2, or even SE CORNER 17832 W OF THE 5TH, NW 7TH 29-3 WEST THE 5TH. Those addresses aren't considered to have a unit number.

Man, it might be easier to just go through all the data manually... this is turning out to be more complicated than I thought. Thank you for your help so far though hoyasaxa215.