Monday, December 2, 2013

Adding Leading Zeros back to ZIP Codes

I don't run into this often, but sometimes there is a need to pad the front or back of a character string with spaces or some character.  (More often I need to strip out extra spaces because I get data from an Oracle database and sometimes I forget to trim my SQL results.)

The most frequent are ZIP codes and ZIP extensions delivered as numbers instead of characters and Excel kills the leading zeros.  If I am just working with the worksheet, this is a simple matter of a formula and copying it down
=REPT("0",5-LEN(D1))&D1

That gets a lot longer if you've got some with the ZIP extension (aka ZIP Plus 4), assuming the ZIP is in column A and the extension is in Column B:
=REPT("0",5-LEN(A1))&A1&IF(AND(LEN(B1)>0,ISNUMBER(B1)),"-" & REPT("0",4-LEN(B1))&B1,"")


Here's my standard VBA function for padding those zeros back in, whether with the extension or not:

Function ZIPWithExt(ZIPin As Single, Optional ExtIn As Integer) As String
    ZIPWithExt = IIf(ZIPin > 99999 Or ZIPin < 0, "#Value", Format(ZIPin, "00000")) & _
                        IIf(ExtIn <> 0, "-" & IIf(ExtIn > 9999 Or ExtIn < 0, "#Value", Format(ExtIn, "0000")), "")
End Function