Sorting Question
Hi OssieMac,
Thanks for your tip. Regards.
"OssieMac" wrote:
You could use text to columns to split the data into 2 columns using the
hyphen as the delimiter. This gives you 2 columns of numbers.
Then sort the data on first column, second column.
In a third column concatenate the 2 columns and reinsert the hyphen between
the values. Example =A1&"-"&B1
Regards,
OssieMac
"Freshman" wrote:
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.
|