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!
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!
Make sure the cells containing the zip codes are formatted as text.
--
Vasant
"Martha Parsons" <Martha [email protected]> wrote in message
news:[email protected]...
> 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!
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 [email protected]> wrote in message
news:[email protected]...
> 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!
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 [email protected]> wrote in message
> news:[email protected]...
> > 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!
>
>
>
On Sat, 21 May 2005 08:57:11 -0700, "Martha Parsons" <Martha
[email protected]> 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
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" <[email protected]> wrote in message news:[email protected]...
> On Sat, 21 May 2005 08:57:11 -0700, "Martha Parsons" <Martha
> [email protected]> 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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks