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… .

### Like this:

Like Loading...

*Related*