Sort a list with different format

[Charles]

Hi All:

I have a column of list containing text records as well as special values.
When I tried to sort this column, text records and special records always go
together. I tried to reformat them, but I lost leading zeros. The sample
list looks like this:

02West
Nacy
1David
001Mary
00033
01354
18056
45000

The first four records are in text format and the rest four records are in
special format (such as zip code). When I tried to sort them, text records
and special records will be sorted separately, that is text records and
special records always go together with each other, respectively.

Any help will be highly appreciated. Thanks in advance.

Charles Deng

 

[Jason Morin]

Add a new column and convert them all to text:

=TEXT(A1&"","00000")

Then select both columns and sort based on this new
column.

HTH
Jason
Atlanta, GA

 

[Charles]

Hi Jason:

Your method works well. Thanks a lot.

Charles