Locations, locations, locations

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:

=TRIM(LEFT(A1, SEARCH(“Unit”,A1)-1))

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?

Share your insights here...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s