Multi-Level Sort

[MarkC]

Hi,

I have this:
4.jpg
3.jpg
40.jpg
30.jpg
5.jpg
200.jpg
2.jpg
1100.jpg
1.jpg
4000.jpg
212.jpg

Sort will do the following:
1.jpg
1100.jpg
2.jpg
200.jpg
212.jpg
3.jpg
30.jpg
4.jpg
40.jpg
4000.jpg
5.jpg

But I want the sort to do this instead:
1.jpg
2.jpg
3.jpg
4.jpg
5.jpg
30.jpg
40.jpg
200.jpg
212.jpg
1100.jpg
4000.jpg

Is it possible and if so how?

Using XP Pro and Excel 2002.

Thanks,

MC

 

[Per Jessen]

Hi MC

You need a helper column.
With your list in column A, insert this formula i B1, and copy it down as
required.

=(LEFT(A1,FIND(".",A1)-1))*1

In an unused cell enter: 1 and copy the cell. Goto Edit > Paste Special >
Multiply. Now column B is seen as numbers. Select columns A:B and goto Data

Sort > Sort by: Column B > OK.

Hopes this helps.

 

[Dave Peterson]

Another way is to use Data|Text to columns (xl2003 menus).

Use Delimited by period
and plop the data into separate columns (even skipping the extension).

Then select the entire range, but sort by the column with the number in it.

 

[MarkC]

Hi Per:

I followed your instructions, and went to an unused cell and entered 1. I
copied it, I then went to Edit, Paste Special > Multiply but don't know
where to paste it to? I couldn't get it to work. Was there a step missing,
or did I do it wrong? Please include more steps. By the way, the data is
not all numeric, these do have a .jpg tag onto to the numbers. And I am
getting a value error after entering the formula.

Thanks,

Mark

 

[MarkC]

Hi Dave:

Not sure what you mean by (x12003 menus)? The rows of data cells does
extend past 256, average rows are 20,000.
If I understand your method I would run out of column space across. Plop
the data into separate columns???

Thanks,

Mark

 

[MarkC]

Hi Per:

Also, some of the data can be like, 020-897.jpg, 25-0547.jpg, and some may
have alpha, 14A.jpg. One common thing, is that the data does start in
numeric.

Thanks,

Mark

 

[Dave Peterson]

xl2007 redesigned the interface of Office.

xl2003 menus meant that using xl2003 (and below), you'd click on Data on
toolbar, then text to columns.

And from your description, you'd end up with 2 (or 1) additional columns. If
you chose to skip the extension, just a single extra column.

 

[Per Jessen]

Hi Mark

Sorry, I forgot to mention it. When you have copied 1, select the helper
column, then Paste Special.

Use Text To Columns as Dave suggest to remove "-" and "."

When names have alpha character like 14A.jpg is it always only one or can
there be more?

Hopes this helps.

Per

 

[MarkC]

Ok, thanks,
M

xl2007 redesigned the interface of Office.

xl2003 menus meant that using xl2003 (and below), you'd click on Data on
toolbar, then text to columns.

And from your description, you'd end up with 2 (or 1) additional columns.
If
you chose to skip the extension, just a single extra column.

 

[MarkC]

Ok, I will give this a try. I can have numbers like, 100_234_567, 1234abc,
100-3_5a-5h

Thanks,

M