Sorting ZIP Codes

Cells may be formatted as ZIP codes from the git-go:

Format cells
Number
Special
ZIP Code _or_ ZIP Code + 4

Based on a quick test, these sort correctly, but only if you stick with ZIP or ZIP+4. If you try to mix the two by adding a 4-digit suffix to the ZIP format, the sort doesn't work. If you try to enter just 5 digits in the ZIP+4 format, the ZIP code fills from the right.

Craig that is a great tip.

Long time ago I learned to format all numbers that will not be used in calculations as text when starting to design databases in MS Access; so it includes telephone numbers, social security numbers, part numbers, etc. If it will not be used in calculating another value then there is no reason to treat it as a number.

If you have many of them, a quick way to convert is to add a helper column. If the zip codes are in column K starting in row 2 and the next empty column is R, then in cell R2, enter the formula
=k2&""
This will convert numbers to text. Copy the formula all the way down. Then copy column R and paste as values only onto column K.

Bruc:

The macro will work on multiple entires. Assime you have a column of 10000 zip codes. Just select the entire column & run the macro.

This works for single entry situations but what about when down loading from a database to excel or csv format? I am often working with thousands of records and cannot do each field individually.

I learned long ago, you always want to use the text format with zip codes. In many north east states, the zip code begins with 0 which will be truncated if the format is General or Number.

Don: Thanks for the hint. It works great!

Jack: The macro will work on a block of cells; just select the block first before executing the macro.

The entire macro, plus Don's line, is as follows:

Sub ConvToTxt()

Dim txt As String
Dim cl As Range

For Each cl In Selection
ActiveCell.NumberFormat = "@"
txt = cl.Value
ActiveCell.FormulaR1C1 = txt
ActiveCell.Offset(1, 0).Select
Next cl
End Sub

Ken: Thanks also for your suggestion to use Text-to-Columns.

---Glenn

Another quick and easy way to mass convert all the cells to TEXT is to select the range of cells you want converted, then use the 'text to columns' function found in the data tab.
I suggest to use the fixed width method and ensure that you set the column break wide enough to cover the longest zip code and then set the column format for that to be TEXT. When you click on the FINISH button excel will convert those selected cells to TEXT format.

Steps 3,4,and 5 in the proposed solution need to be done for each individual cell.

Is there a way to do those steps for a block of cells?

Glenn

The piece that is missing is changing the formatting of the cell to Text. Here is the line (editted for quick insert to your

ActiveCell.NumberFormat = "@"

So any ideas on how to format existing entries as text in bulk or via a macro?

I tried copying and pasting such entries into pre-formatted cells using Paste Special/Values. No luck. I also tried the following macro:

Sub ConvToTxt()

Dim txt As String
Dim cl As Range

For Each cl In Selection
txt = cl.Value
ActiveCell.FormulaR1C1 = txt
ActiveCell.Offset(1, 0).Select
Next cl
End Sub

Also no luck. Any thoughts?

---Glenn