Hi Arvi,

Thanks for your tip. Regards.

"Arvi Laanemets" wrote:

Hi

To avoid such problems in future, have the input box numbers in format like
this:
2005-00101
2005-01018
2006-02009
2005-00673
2006-00399
2006-00260

To convert all existing box numbers to new one:
Into a free cloumn, enter the formula (I assume existing numbers are in
column A, and the 1st is in A2 - so the formula you inter into row 2)
=LEFT(A2,4) & "-" & TEXT(MID(A2,6,10),"00000")
(modify the formula, when you want a different number of leading 0's)
Copy the formula down for all rows containing old box numbers.
Copy the range with formulas, and use PasteSpecialValues to replace old
numbers with new ones.
Delete the column with formulas.

Now you can sort your data properly. And be sure new data are entered
properly too.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Freshman" wrote in message
...
Dear experts,

I've a table and one of the columns is for the box numbers. The users
usually input the box number as "2006-101" which 2006 represents the year
and
101 as the box equence number. When I consolidate all data together, the
box
numbers are in the order below:

2005-101
2005-1018
2006-2009
2005-673
2006-399
2006-260

When I click the sort button (in ascending order), the numbers are in the
order like:

2005-101
2005-1018
2005-673
2006-2009
2006-260
2006-399

My question is, how to sort the numbers so that the year prefix will come
first then the box numbers, such as: 2005-673 will go before 2005-1018 and
2006-260 & 2006-399 will go before 2006-2009? If I split this column into
two
and then sort two columns separately, it may work but it seems a bit
clumsy.

Please advise the best ways.

Thanks in advance.