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.


--

Nigel M

If I'm discussing apples, please don't suggest bananas

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



On Oct 14, 7:44*am, Nigel Molesworth
wrote:
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?

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.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=144059

On Wed, 14 Oct 2009 14:11:18 +0100, joel
wrote:

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?

This will give you the leading letters...

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

and this will give you the trailing numbers...

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

--
Rick (MVP - Excel)


"Nigel Molesworth" wrote in message
...
On Wed, 14 Oct 2009 14:11:18 +0100, joel
wrote:

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?

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

"Nigel Molesworth" wrote in message
...
On Wed, 14 Oct 2009 14:11:18 +0100, joel
wrote:

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?