Spreadsheet Fun– Excel

Suppose you had a list of addresses coded like this:

00014 SOUTH ST

That you’d prefer coded like this:


If you were addicted to modifying things programmatically in Excel, like I quite guiltily do, you could do this in MS Excel.

=(MID(A1,1,FIND(" ",A1)-1))*1 & MID(A1,FIND(" ",A1),LEN(A1))

Everything before the ampersand (&) uses the =mid() function to grab everything up until the first space and multiplies it by one.  Excel automatically does a text to number conversion if you do any mathematical manipulations, so this converts our text to a number, therefore trimming off the spare zeros.  You could use the =value() worksheet function here, but why bother?  After the ampersand, we use the mid function to grab everything after and including the first space.  The concatenation of the two is our cleaned up address.

I know.  Not really free and open source GIS at all… .

Leave a Reply

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.