Help with sorting!

[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

 

[swatsp0p]

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

 

[couriced]

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..

 

[couriced]

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.

 

[swatsp0p]

in a helper column use the TRIM function, e.g.

=TRIM(A1)

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

Good Luck

 

[couriced]

Bingo...TY!

 

[swatsp0p]

I'm glad it worked for you. Thanks for the feedback, it is alway
appreciated.

Cheers