Excel 2002: How to sort text with different lenght ?
 

Dear Sir,

I find that Excel 2002 could sort alphanumeric references of the same length
very well but not the references with different number of character as
illustrated below.

Same Length Different Length
(No problem in sorting) (Unable to sort in this sequence)
FG0001 FG1
FG0012 FG12
FG0241 FG241
FG6806 FG6806


For references with different number of character, Excel could only group
the same item together but not in ascending or descending order.

Is there anyway for it to sort in sequence?


Thanks

Low


--
A36B58K641

Excel 2002: How to sort text with different lenght ?
 

Yes there is a way, not particularly 'handy', but there is a way.

The problem you've encountered is not Excel unique - it applies to any
sorting of ASCII text that includes numeric characters. Just the nature of
the beast.

To sort your group, assuming they are in the format of the 'Different
Length' examples: 2 alpha characters followed by from 1 to 4 numeric
characters, total lenght desired always 6, then put this formula into a cell
on the same row and fill it to the end of your list:
=LEFT(A2,2) & REPT("0",6-LEN(A2)) & RIGHT(A2,LEN(A2)-2)

In that example, A2 would be the cell with an entry like "FG1" or "FG241".
Then to sort, include this helper column in the to-be-sorted range and use it
as the key field to sort by.

To explain the formula, first part: LEFT(A2,2) simply echos the 1st 2
characters in A2 - the 2 alpha characters.
Second part: REPT("0",6-LEN(A2)) says repeat the character "0" 6-length of
text in A2 times. Since we've agreed max length is to be 6, that's why 6 was
used. When there's an entry like 'FG1' it returns 6-3 = 3 zeros, when
'FG1234' is in it, 6-6=0 zeros. If 'FG12345' then you get an error!

Last part: RIGHT(A2,LEN(A2)-2) just echos all characters to the right of the
2 alpha characters.

"Mr. Low" wrote:

Dear Sir,

I find that Excel 2002 could sort alphanumeric references of the same length
very well but not the references with different number of character as
illustrated below.

Same Length Different Length
(No problem in sorting) (Unable to sort in this sequence)
FG0001 FG1
FG0012 FG12
FG0241 FG241
FG6806 FG6806


For references with different number of character, Excel could only group
the same item together but not in ascending or descending order.

Is there anyway for it to sort in sequence?


Thanks

Low


--
A36B58K641

Excel 2002: How to sort text with different lenght ?
 

You could turn your Different Length strings into Same Length strings
by means of a formula like this in a helper column:

=LEFT(A1,2)&TEXT(RIGHT(A1,LEN(A1)-2),"0000")

and copy down as needed. Alternatively, you could just extract the
number part into a helper column with this:

=VALUE(RIGHT(A1,LEN(A1)-2))

and copy this down.

Either way, you then use the helper column as the sort key.

Hope this helps.

Pete


On Jul 16, 2:34 pm, Mr. Low wrote:
Dear Sir,

I find that Excel 2002 could sort alphanumeric references of the same length
very well but not the references with different number of character as
illustrated below.

Same Length Different Length
(No problem in sorting) (Unable to sort in this sequence)
FG0001 FG1
FG0012 FG12
FG0241 FG241
FG6806 FG6806

For references with different number of character, Excel could only group
the same item together but not in ascending or descending order.

Is there anyway for it to sort in sequence?

Thanks

Low

--
A36B58K641

Excel 2002: How to sort text with different lenght ?
 

Dear Sir,

Thanks for your detail explanation.

Kind Regards

Low

--
A36B58K641


"JLatham" wrote:

Yes there is a way, not particularly 'handy', but there is a way.

The problem you've encountered is not Excel unique - it applies to any
sorting of ASCII text that includes numeric characters. Just the nature of
the beast.

To sort your group, assuming they are in the format of the 'Different
Length' examples: 2 alpha characters followed by from 1 to 4 numeric
characters, total lenght desired always 6, then put this formula into a cell
on the same row and fill it to the end of your list:
=LEFT(A2,2) & REPT("0",6-LEN(A2)) & RIGHT(A2,LEN(A2)-2)

In that example, A2 would be the cell with an entry like "FG1" or "FG241".
Then to sort, include this helper column in the to-be-sorted range and use it
as the key field to sort by.

To explain the formula, first part: LEFT(A2,2) simply echos the 1st 2
characters in A2 - the 2 alpha characters.
Second part: REPT("0",6-LEN(A2)) says repeat the character "0" 6-length of
text in A2 times. Since we've agreed max length is to be 6, that's why 6 was
used. When there's an entry like 'FG1' it returns 6-3 = 3 zeros, when
'FG1234' is in it, 6-6=0 zeros. If 'FG12345' then you get an error!

Last part: RIGHT(A2,LEN(A2)-2) just echos all characters to the right of the
2 alpha characters.

"Mr. Low" wrote:

Dear Sir,

I find that Excel 2002 could sort alphanumeric references of the same length
very well but not the references with different number of character as
illustrated below.

Same Length Different Length
(No problem in sorting) (Unable to sort in this sequence)
FG0001 FG1
FG0012 FG12
FG0241 FG241
FG6806 FG6806


For references with different number of character, Excel could only group
the same item together but not in ascending or descending order.

Is there anyway for it to sort in sequence?


Thanks

Low


--
A36B58K641

Excel 2002: How to sort text with different lenght ?
 

Dear Sir,

Thanks for your helpful information.


Best Regards

Low

--
A36B58K641


"Pete_UK" wrote:

You could turn your Different Length strings into Same Length strings
by means of a formula like this in a helper column:

=LEFT(A1,2)&TEXT(RIGHT(A1,LEN(A1)-2),"0000")

and copy down as needed. Alternatively, you could just extract the
number part into a helper column with this:

=VALUE(RIGHT(A1,LEN(A1)-2))

and copy this down.

Either way, you then use the helper column as the sort key.

Hope this helps.

Pete


On Jul 16, 2:34 pm, Mr. Low wrote:
Dear Sir,

I find that Excel 2002 could sort alphanumeric references of the same length
very well but not the references with different number of character as
illustrated below.

Same Length Different Length
(No problem in sorting) (Unable to sort in this sequence)
FG0001 FG1
FG0012 FG12
FG0241 FG241
FG6806 FG6806

For references with different number of character, Excel could only group
the same item together but not in ascending or descending order.

Is there anyway for it to sort in sequence?

Thanks

Low

--
A36B58K641