Postal Code Fixer

January 24, 2007

You might encounter problems with US Postal Codes when entered into Excel. The standard format is 5 digits XXXXX. Being such, there areas that starts with Zero, 0XXXX. The same goes with the additional 4 digits, XXXX-NNNN. NNNN could take in Zero as the first digit, 0NNN.

This issue could lead to a Postal Code of only four digits and the extension with only three digits.

Solution:

=IF(LEN(I2)=3,H2&”-0″&I2,H2&”-“&I2)

=IF(Len(J2)=4, “0”&IF(LEN(K2)=3,J2&”-0″&K2,J2&”-“&K2), IF(LEN(K2)=3,J2&”-0″&K2,J2&”-“&K2))

Then format the column as text.

Advertisements

Excel Phone Number Cleaner

January 24, 2007

Phone numbers from a list contains characters that are not standardized. There are unwanted characters like period, dashes or hyphens, parenthesis, braces, brackets, sapces, slashes, etc.

One of the things I do is to reprocess those columns using the Excel formulas below so that what I get is a clean 10-digit phone number.

Just copy and paste it to a blank column on your excel sheet then change the given cell to your actual cell.

=Substitute(substitute(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(H2, “-“, “”), “(“, “”), “)”,””), ” “, “”), “*”, “”), “+”, “”), “=”, “”), “/”, “”)

=Substitute(substitute(Substitute(SUBSTITUTE(SUBSTITUTE(K2,”.”,””), “`”, “”), “_”, “”),”{“,””), “}”, “”)

=RIGHT(I2,10)

=len(H2)


2007: Time to roll-out the plans

January 1, 2007

2007, welcome!!!

A part of 2006 was spent on plans, preparations, research, and internalization for this project.

Time to roll-out the plans… =)