Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you sort 9 digit zip codes in Excel?
Nine digit zip codes do not sort properly in Excel even when the cell is
formatted as "Special". I use Excel to sort for mailing labels and cannot use 9 digit zipcodes because of the error. Help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you sort 9 digit zip codes in Excel?
Make sure the cells containing the zip codes are formatted as text.
-- Vasant "Martha Parsons" <Martha wrote in message ... Nine digit zip codes do not sort properly in Excel even when the cell is formatted as "Special". I use Excel to sort for mailing labels and cannot use 9 digit zipcodes because of the error. Help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you sort 9 digit zip codes in Excel?
in an adjacent column put in a formula like (assume the zip is in C1)
=if(len(C1) = 5,text(C1,"00000")& "-0000",Text(C1,"00000-0000")) Drag fill down the column, then sort on that column. -- Regards, Tom Ogilvy "Martha Parsons" <Martha wrote in message ... Nine digit zip codes do not sort properly in Excel even when the cell is formatted as "Special". I use Excel to sort for mailing labels and cannot use 9 digit zipcodes because of the error. Help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you sort 9 digit zip codes in Excel?
Thank You!!!!!!!! This worked beautifully!
Martha "Vasant Nanavati" wrote: Make sure the cells containing the zip codes are formatted as text. -- Vasant "Martha Parsons" <Martha wrote in message ... Nine digit zip codes do not sort properly in Excel even when the cell is formatted as "Special". I use Excel to sort for mailing labels and cannot use 9 digit zipcodes because of the error. Help! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you sort 9 digit zip codes in Excel?
On Sat, 21 May 2005 08:57:11 -0700, "Martha Parsons" <Martha
wrote: Nine digit zip codes do not sort properly in Excel even when the cell is formatted as "Special". I use Excel to sort for mailing labels and cannot use 9 digit zipcodes because of the error. Help! Can you give an example of improperly sorted 9 digit zip codes? Although I think I know what you mean, I was not able to replicate a sorting error. --ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you sort 9 digit zip codes in Excel?
Martha (the OP), indicated she had the answer at the same time
you replied, evidently just new entries are the problem. If there were a number of old entries you would want to convert them to text, and I have a macro for that. http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5 Tom, a zip code of 7234 formatted as 00000 to show 07234 would test as a length of 4 =LEN(A1) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Ron Rosenfeld" wrote in message ... On Sat, 21 May 2005 08:57:11 -0700, "Martha Parsons" <Martha wrote: Nine digit zip codes do not sort properly in Excel even when the cell is formatted as "Special". I use Excel to sort for mailing labels and cannot use 9 digit zipcodes because of the error. Help! Can you give an example of improperly sorted 9 digit zip codes? Although I think I know what you mean, I was not able to replicate a sorting error. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I zip sort mailing list when I have 5 digit AND 9 digit zip | Excel Discussion (Misc queries) | |||
Sorting by 5 digit & 5 digit plus 4 zip codes | Excel Worksheet Functions | |||
Need to convert list of 5 digit zip codes to ranges where possible | Excel Worksheet Functions | |||
only 5 digit zip codes | Excel Discussion (Misc queries) | |||
Tell users how to sort 5 digit and 9 digit zipcodes correctly aft. | New Users to Excel |