Help with sorting!

  • Thread starter Thread starter couriced
  • Start date Start date
C

couriced

Hello,

Is there an easy way to sort a list with varying lengths of data?

We have a column that has data in the cells that are say 4-9 character
in length. What currently happens when we sort is it sorts all the
character length entries first, then the 5 character entries, and s
on. We would like to sort it so it is alphanumeric based on th
characters it contains...regardless of length.

Thanks in advance
 
Shouldn't happen that way, can you give some specific cell contents t
evaluate?

If I have the following in a column:

1abc
2ab
b412
1a
'4 (a text entry)
b2135
and sort Ascending, I get:

1a
1abc
2ab
4
b2135
b412

What are you getting
 
Yes, agreed. When I do a quick sample myself...I get the same result a
you. User just emailed me their workbook...and it will only sort thi
way:

BO33
AT283
AT384
AT386
AT645
AT651
DA008
DAA&M
NY321
NY548
NY585
NY950
PH258
PH259
PH261
PH262
PH263
PH264
PH266
PH267
PH268
PH269
PH272
PH560
AT1060
AT1061

I have tried just copying this one column out of the workbook an
pasting to its own and formatting it as text to try...it just won't..
 
Ok..after playing around with the users file...I have discovered why it
won't sort...which opens a new problem. This data was an export from
elsewhere. Not obvious at first glance is that these values all have a
random number of spaces before them. When I goto the cell and delete
the space that precedes the actual data...then resort...that particular
cell then falls in place where we want in a sort. Now...how do we delete
a random number of spaces before each piece of data. Looks like there's
2-4 of them.
 
in a helper column use the TRIM function, e.g.

=TRIM(A1)

"__1234" will become "1234" (leading spaces removed)

Good Luck
 
Back
Top