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.

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

One way ..

Assume data in A1 down
In B1: =LEFT(A1,4)+0
In C1: =RIGHT(A1,LEN(A1)-5)+0
Select B1:C1 copy down.
Then select cols A to C, click DataSortSort by Col B (ascending), Then by
Col C (ascending). Click OK.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"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.

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.

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.

Hi Max,

Thanks for your help again. Best regards to you and your family.

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

Hi Harlan,

Thanks for your tips. Regards.

"Harlan Grove" wrote:

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

Hi Max,

Thanks for your help again. Best regards to you and your family.

"Max" wrote:

One way ..

Assume data in A1 down
In B1: =LEFT(A1,4)+0
In C1: =RIGHT(A1,LEN(A1)-5)+0
Select B1:C1 copy down.
Then select cols A to C, click DataSortSort by Col B (ascending), Then by
Col C (ascending). Click OK.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"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.

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.

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.

welcome, Freshman. Glad to help.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Freshman" wrote in message
...
Hi Max,

Thanks for your help again. Best regards to you and your family.

XL is seeing these entries as text, so..
just enter them like so 20060101 without the hyphen and be sure you use 8 digits.

Custom format these cells with a "0000-0000" number format.

GL

Regards
Robert McCurdy

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

----------------------------------
There is always more than one way Harlan :)