Sorting numbers

[billfan]

I'm trying to sort a list of codes I have in one column in excel, about 100 rows worth. The problem that I'm having is that I want to sort them in overall increasing chronological order, but Excel keeps putting the codes with letters at the end of them in their own order at the end, after all the "number only" codes are done being sorted. They look like this

09879
11334
14589
34875
36527
54091
54283
75893
99211
23143T
36345F
38456F
59840B
59840D

But I want them to sort like this:
14589
23143T
34875
36345F
36527
38456F
54091
54283
59840B
59840D
75893
99211

I'm using AutoFilter, then the Data-->Sort function, but it keeps sorting them the first way. I need it the second way. Any thoughts? Thanks in advance!

 

[hatman]

hatman,

you would be a good lawyer
thanks

I prefer to think that I make a good engineer because I think like a lawyer.

 

[Peter_SSs]

Yup... try it. If you simply concatenate a space or a logical blank to turn the numbers into text, Excel STILL insists on interpretting those number-like strings as numerals, and you haven't made any gains... by concatenating some text character (could be anything, as long as it is the same for everything) it defeats Excel's insistance on second-guessing that it's a numeral because it looks like one, even though it's reallly text.

Edit: I did the same double take... which prompted my previous post

Well, I had tried both
="&" & A1
and
="" & A1
before asking my question.

The only differenece that I could see was that during the sort (having used the second formula) I was asked if I wanted to:
'Sort anything that looks like a number as, a number' or
'Sort numbers and numbers stored as text separately'
Having chosen the second option, I could find no differenece with the results.

So while Erik's suggestion did save two extra clicks in the sort process (and I guess that is worth saving) I was fishing to see if there was any other logical/efficiency/accuracy/robustness reason for choosing Erik's formula.

 

[erik.van.geit]

Hi, Peter,

in fact I didn't think a lot about this
the solution with "" is better if you want to convert the items to text ...

..hmm ... thinking loudly ...

this could lead to another solution
just convert the items to text using text to columns (last box select datatype = text)
then sort

same result

kind regards,
Erik

 

[hatman]

[quoteWell, I had tried both
="&" & A1
and
="" & A1
before asking my question.[/quote]

Me too. Before I even posted my original response, I had tried =" " & A1, as well as =text(a1,"#")... and even simply adding a single quote before each entry... and in no case, did Excel ask me if I wanted to sort as numbers or not... and in all 3 cases, the result was incorrect. After Erik Posted, I did the same comparison, adding his solution to the matrix... and voila unlike my other solutions, the results were correct... now this morning, I repeated my trial, and as you point out, the results are correct for trials that didn't work yesterday. I just don't get it I must have something differently, but I have no idea what....