"Freshman" wrote...
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

These are text.

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

This is how text is ordered. Just like "aaaa" comes before "ab", "1018"
comes before "673".

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.

The ONLY ways you could do this require two columns. Either split these
entries in two at the dash, or use formulas in the second column to
transform this text into something that could be processed as numbers. For
instance, if your sequence numbers would never exceed 6 numerals,

=LEFT(x,4)*1000000+MID(x,FIND("-",x)+1,6)

would turn these into numbers, 2005-673 would become 2006000673. Sort both
columns plus whichever others also need to be sorted by these values by the
column of these formulas. Then you could delete, clear or hide the column of
formulas. Clumsy or not, you have no alternative.