SORT MIXED DATA

[jem]

I am trying to sort a spreadsheet which has part numbers as the main
description (ie: 500111, 500123, 500111A, 500111B, 500123A ...) I want the
sort option to sort the part number column with it's associated data in
numerical order including the letters (ie: 500111, 500111A, 500111B, 500123,
500123A ... )
Anyone know how I can achieve this goal?

[N Harkawat]

If your alpha numeric part number is always 6digits number and the rest
alphabet(s) then
insert a column next to the part list and trype

=--(left(a1,6)) and copy it down
Now sort based on this new column selecting all the columns




"jem" <[email protected]> wrote in message
news:[email protected]...
>I am trying to sort a spreadsheet which has part numbers as the main
> description (ie: 500111, 500123, 500111A, 500111B, 500123A ...) I want the
> sort option to sort the part number column with it's associated data in
> numerical order including the letters (ie: 500111, 500111A, 500111B,
> 500123,
> 500123A ... )
> Anyone know how I can achieve this goal?

[Jason Morin]

Use a helper column with the following formula:

=IF(ISERROR(A1*1),LEFT(A1,LEN(A1)-1),A1&"")

where A1 contains the part #. Fill the formula down,
select both columns, and sort ascending, first on the
formula column, then on the part column.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I am trying to sort a spreadsheet which has part numbers
as the main
>description (ie: 500111, 500123, 500111A, 500111B,
500123A ...) I want the
>sort option to sort the part number column with it's
associated data in
>numerical order including the letters (ie: 500111,
500111A, 500111B, 500123,
>500123A ... )
>Anyone know how I can achieve this goal?
>.
>

[d]

you can use an @mid function to "strip off" the first 6
numeric characters and put into a new column, sort on this
formula with a secondary sort on the alphanumeric part
number to order the alpha numbers.


>-----Original Message-----
>I am trying to sort a spreadsheet which has part numbers
as the main
>description (ie: 500111, 500123, 500111A, 500111B,
500123A ...) I want the
>sort option to sort the part number column with it's
associated data in
>numerical order including the letters (ie: 500111,
500111A, 500111B, 500123,
>500123A ... )
>Anyone know how I can achieve this goal?
>.
>