Excel Sort alphanumeric? 3

[kirstenlargent]

I have a sort in Excel that is giving me a fit. Here are the Department Codes that I am trying to sort:
11903136655
119059921C7
119077921
51905799331
The above list is the order that I want them to be in. To me, 119031... should come before 119059...
But here is the order that Excel is sorting them in:
119077921
11903136655
51905799331
119059921C7
It does this regardless of the format (general, number, text). The only work around I have found is to separate the column into 4 separate columns with three digits in each. This works but is a pain.
Any suggestions on how I can get it to sort correctly off of just one column?

 

[xlbo]

Sounds to me like some of your data is text and some is numbers (you can test this by using =ISTEXT(A1) and =ISNUMBER(A1) ). Excel is sorting correctly as it puts numeric 1st and then alpha - ie your data with text in is treated as Alpha - the data with no text is treated as numeric - you need to make it all text. You CAN'T DO THIS BY FORMATTING. Formatting only changes the APPEARANCE of data NOT what is actually there.

You need to use the TEXT function on the list you want sorted
=TEXT(A1,"@&quot
should do the trick (where A1 has data)
then, copy your new data and paste special values over the old data. Then sort. I'm willing to bet (tho not much) that it'll work after that Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[GlennUK]

This is because you Department Code that have no alphabetic characters are being treated as numbers.

In another column enter this formula
=TEXT(A1,"0&quot
and copy down as necessary.

Select these new cells and do Edit/Copy.
Now paste over the original values using Edit/Paste Special/Values and your sorting should work properly.

Glenn.

 

[xlbo]

Heh heh heh - got there 1st ;-) Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[DaleWatson123321]

Another option...

In the adjacent column (B for example), use the formula: =LEFT(A1,xxx) - where you replace "xxx" with the largest number you can use before including an alpha character in any of the numbers.

In the alphanumeric example you gave, 119059921C7... you could use =LEFT(A1,9)

Then sort on both column A & B, but use column B as your primary sort field.

Once you've copied the formulas for Column B, you can hide Column B - and leave it hidden while you execute the sort.

To do this manually...

1) Hit the <GoTo> key <F5> and enter A:B

2) From the menu, use: Data - Sort

3) Choose &quot;Header Row&quot; IF you have headers. Otherwise choose &quot;No header row&quot;, and then specify Sort by: Column B.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[GlennUK]

Well done Geoff,

I couldn't believe it after I pressed submit ... there you were!

Cheers, Glenn.

 

[kirstenlargent]

Thanks - ALL of these options worked perfectly! The TEXT function was the easiest and quickest. Thanks again!

 

[KenWright]

Just as an addendum, had you wanted to make all the data numeric, you could simply copy an empty cell, select all the data and do Edit / Paste Special / Add. This will coerce Excel into treating it all as numeric data.

Only time to be careful is if you have leading 0s you need to kepp, and also if your data is greater than 15 digits, as Excel will round to 0 after 15 significant figures.

Regards
Ken...................

 

[xlbo]

Also - some of the data is alphanumeric and therefore CANNOT be converted to numeric....(well it can but only char representations of letters) Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[Cresby]

isn't there an option with three sort choices alpha, alpha-numeric and ascii (not at my PC so can't check). Or am I confusing the VBA sort command (maybe for lists) with the menu. But yes I would have asked about mixed text & numbers if the various formats had not been tried. I have had this sort (sic) of problem and I found a way to alter &quot;sort&quot; but the results are in a worksheet somewhere not in the brain.