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 characters
in length. What currently happens when we sort is it sorts all the 4
character length entries first, then the 5 character entries, and so
on. We would like to sort it so it is alphanumeric based on the
characters it contains...regardless of length.

Thanks in advance!


--
couriced
------------------------------------------------------------------------
couriced's Profile: http://www.excelforum.com/member.php...o&userid=26900
View this thread: http://www.excelforum.com/showthread...hreadid=475956

Shouldn't happen that way, can you give some specific cell contents to
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?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=475956

Yes, agreed. When I do a quick sample myself...I get the same result as
you. User just emailed me their workbook...and it will only sort this
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 and
pasting to its own and formatting it as text to try...it just won't...


--
couriced
------------------------------------------------------------------------
couriced's Profile: http://www.excelforum.com/member.php...o&userid=26900
View this thread: http://www.excelforum.com/showthread...hreadid=475956

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.


--
couriced
------------------------------------------------------------------------
couriced's Profile: http://www.excelforum.com/member.php...o&userid=26900
View this thread: http://www.excelforum.com/showthread...hreadid=475956

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

=TRIM(A1)

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

Good Luck


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=475956

Bingo...TY!


--
couriced
------------------------------------------------------------------------
couriced's Profile: http://www.excelforum.com/member.php...o&userid=26900
View this thread: http://www.excelforum.com/showthread...hreadid=475956

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

Cheers!


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=475956