Excel Formula to Split Address into line 1 and line 2
Given full street address plus apartment/unit number in a cell, how to split into two cells, one for street 1, and one for the apt/unit #?
Here is a formula using common delimiters such as 'unit' or 'suite'. Add more as needed.
Replace "H2" with the cell that has your street address.
LINE 1
=IF(ISNUMBER(SEARCH(" Ste. ", H2, 1)), MID(H2, 1, SEARCH(" Ste. ", H2, 1)),
IF(ISNUMBER(SEARCH(", Suite ", H2, 1)), MID(H2, 1, SEARCH(", Suite ", H2, 1)-1),
IF(ISNUMBER(SEARCH(", Space ", H2, 1)), MID(H2, 1, SEARCH(", Space ", H2, 1)-1),
IF(ISNUMBER(SEARCH(" STE ", H2, 1)), MID(H2, 1, SEARCH(" STE ", H2, 1)-1),
IF(ISNUMBER(SEARCH(" Unit ", H2, 1)), MID(H2, 1, SEARCH(" Unit ", H2, 1)-1),
IF(ISNUMBER(SEARCH(" Suite ", H2, 1)), MID(H2, 1, SEARCH(" Suite ", H2, 1)-1),
IF(ISNUMBER(SEARCH(", #", H2, 1)), MID(H2, 1, SEARCH(", #", H2, 1)-1),
IF(ISNUMBER(SEARCH(" #", H2, 1)), MID(H2, 1, SEARCH(" #", H2, 1)-1), H2)
)
)
)
)
)
)
)
LINE 2
=IF(ISNUMBER(SEARCH(" Ste. ", H2, 1)), MID(H2, 1+ SEARCH(" Ste. ", H2, 1),999),
IF(ISNUMBER(SEARCH(", Suite ", H2, 1)), MID(H2, 2+SEARCH(", Suite ", H2, 1),999),
IF(ISNUMBER(SEARCH(", Space ", H2, 1)), MID(H2, 2+SEARCH(", Space ", H2, 1),999),
IF(ISNUMBER(SEARCH(" STE ", H2, 1)), MID(H2, 1+SEARCH(" STE ", H2, 1),999),
IF(ISNUMBER(SEARCH(" Unit ", H2, 1)), MID(H2, 1+SEARCH(" Unit ", H2, 1),999),
IF(ISNUMBER(SEARCH(" Suite ", H2, 1)), MID(H2, 1+SEARCH(" Suite ", H2, 1),999),
IF(ISNUMBER(SEARCH(", #", H2, 1)), MID(H2, 1+SEARCH(", #", H2, 1),999),
IF(ISNUMBER(SEARCH(" #", H2, 1)), MID(H2, 1+SEARCH(" #", H2, 1),999), "")
)
)
)
)
)
)
)
Created By: amos 2/13/2014 3:27:14 PM
Updated: 2/13/2014 3:27:27 PM
|
|