Help With Sort

[Dazzawm]

I have some data which will not sort how I want it to. It is in the format of 4 letters and either 3 or 4 digits as below.

ABCD123
ABCD1024
ABCD683 etc etc

When I sort it only does it like ABC all the ones then all the twos but I want it to sort in numerical value i.e ABC then all the numbers to 1000 then ABC then all the numbers to 2000 and so on..

 

[kidwispa]

Assuming your data is in column A (from A2 down), then a quick fix would be to insert a helper column, and use the formula in B2 and copy down:

=MID(A2,4,4)

This will show all of the numerical values from your data... use these to sort by instead...

EDIT - Don't think this does exactly what you are after... instead use text to columns to split the data then sort by ABC... then by number...

 

[mikerickson]

I'm not sure what sort order you want. With this data, what order do you want them it

ABCD1034
ABCD123
BRFQ1034
ABCD286
ABCD2015
ABCD386
BRFQ280

 

[Dazzawm]

I'm not sure what sort order you want. With this data, what order do you want them it

ABCD1034
ABCD123
BRFQ1034
ABCD286
ABCD2015
ABCD386
BRFQ280

ABCD123
BRFQ280
ABCD286
ABCD386
ABCD1034
BRFQ1034
ABCD2015

This is how I would want it. Please note all the letters will be the same

 

[moonfish]

kidwispa's solution works best, except you'll have to change the first 4 to a 5:
=MID(A2,5,4)