Smathermather's Weblog

Remote Sensing, GIS, Ecology, and Oddball Techniques

Spreadsheet Fun– Excel

Posted by smathermather on December 21, 2009

Suppose you had a list of addresses coded like this:

00014 SOUTH ST

That you’d prefer coded like this:

14 SOUTH ST

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
%d bloggers like this: