Sorting alpha-numerically?

[Nigel Molesworth]

I've got an Excel file with a column holding text data such as:

[B11], [B1], [A2], [B100], [B010], [A1] - where [] is the cell

which I want to sort as:

[A1], [A2], [B1], [B010], [B11], [B100]

Note that the leading zeroes (as in [B010], which equals [B10]) are not
required, so I could happily lose them before sorting.

Any suggestions on how to do this?



Also, some of the cells contain multiple data, as in [B2, B13, A4] which I
would ideally like to split off into new columns, or onto duplicated rows.

[Banana] [B2] [B13] [A4]

or

[Banana] [B2]
[Banana] [B13]
[Banana] [A4]

This latter problem is not so important to me at this stage, but I mention
it in case it is of interest.

 

[JP]

Excel version?

In 2003, highlight the data you want to sort and go to Data > Sort.
Click "Options" and choose "Sort left to right".

--JP

 

[Nigel Molesworth]

I use auxilary columns and split the B10 into two columns putting the B
in one column and the 10 in a second column and then sort on the two new
columns

I can see how that would work, but how would I split the data?

 

[Rick Rothstein]

This will give you the leading letters...

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)

and this will give you the trailing numbers...

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)

 

[Peggy Shepard]

Hi Nigel,

=ADDRESS(ROW(INDIRECT(A1)),COLUMN(INDIRECT(A1)))
copy the result as values
text to columns using the "$" character
sort on two levels

Let me know if this helps.

Peggy