Sorting Numbers as Text

[Nikki]

I have an excel worksheet (someone else created) with codes in column
A. The codes are alphanumeric with 5 characters. I want to sort by
column a. I want it to sort left to right, 0 to 9, A to Z.

For instance, I want this list

WW001
00036
4E001
146E1
10142

To sort like this

00036
10142
146E1
4E001
WW001

I have tried formatting everything as text then sorting. I get the
prompt to choose between sorting anything that looks like a number as
a number or sorting numbers stored as text separately.

Sorting anything that looks like a number as a number gives me this:
00036
4E001
146E1
10142
WW001

Sorting numbers stored as text separately gives me this:
10142
00036
146E1
4E001
WW001

When I try this list only in a new excel file I get the expected order
with sort numbers stored as text separately. So I don't understand
why this option is not working on my original worksheet. It's
thousands of rows of data, so it's not something that one would want
to redo. I've tried creating a new row, formatted as text then
copying and pasting values only of the code in the new column. The
column still sorts in those strange orders. (Strange to me anyway)

Any suggestions would be much appreciated. I've spent a lot of time
searching the groups and I can't find a previous solution that works
for me.

 

[Pete_UK]

As some of your codes have an "E" in them with numbers either side,
these will be interpreted by Excel as being numbers, which is why you
get the strange sort order.

Let's assume you have other columns in your data (eg from column A to
column F), then highlight column A by clicking on the column
identifier, and copy/paste to the next available column (G). Then with
column G highlighted, do a Find & Replace (CTRL-H) to change E to some
other character(s) that are not currently used in your codes - for
example Ez. Then in the column next to that you can enter this
formula in H2 (assuming you have a header row):

=IF(ISERROR(VALUE(G2)),G2,VALUE(G2))

and copy this down the column for as many entries as you have in
column G.

Now, highlight all your data plus the two new columns and do a sort
using column H as the key field - you should get your numeric codes
first followed by the alphanumeric codes. You can then delete columns
G and H.

It's difficult to conclude from your sample if this will be enough, as
it depends on the range of codes in your sample, but it should get you
somewhere nearer. Post back to let us know how you get on

Hope this helps.

Pete

 

[Nikki]

Thank you for your reply.

This did help. I originally wanted the numeric codes to be sort in
with the alphanumeric codes, but at least this is a predictable order
for me.

I also took the find and replace step further with this formula
=SUBSTITUTE(B2, "E", "Ez").

Thanks so much for your help.

 

[Pete_UK]

Thanks for feeding back, Nikki. I'm glad you were able to make it
work.

Another way of achieving the sort order you require is to obtain a
list of the unique codes that you have, maybe on a separate sheet, and
to get these in the order that makes sense to you. Then you can fill a
column next to these unique codes with a simple sequence - 1, 2, 3
etc.

You could then use this as a simple lookup table and in your main
sheet you can return the sequence in a helper column and then sort on
this column.

Pete