I love a messy address list where everything is in one column and the delimiters aren’t consistent. Working to parse, clean up, and verify the data points can be fast (especially when you set a timer or an ambitious deadline) and fun, the results satisfying. There’s only so much that filtering and Find/Replace can do though. Here are a couple favorite address-related formulas in Excel, and data people, feel free to share formulas and shortcuts in the comments:
Return the street address with the apartment numbers removed, where it says “Unit” in the full address:
Return the apartment number, where it says “Unit” in the full address:
=TRIM(MID(A1,SEARCH(“Unit “,A1)+0,LEN(A1)-SEARCH(” Unit”,A1)))
And my nomination for the United States town with the coolest street addresses is Plato Township, Illinois. They have alphanumeric street numbers! What are some other interesting address towns whose data you’ve worked with or in which you’ve lived?