SORT MIXED DATA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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
 
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.
 
Back
Top