Sorting a column with dashes, numbers and characters!!

[AmosA]

How do I sort a file that has two columns A & B.

This file will be used to print labels for a racking system in our warehouse. We will use a different colored label for the printed barcode depending on the level. ( For example Level one will be printed on white labels, Level two will be printed on yellow labels.)


Column A is the Location ID # and Column B is the Location Name, the two are link to each other.


Location Name A-00-L1

(A) is the physical row in the warehouse,
(00) is a location in that row
(L1) is the level in that location.


I need the data to be sorted by the 2nd column as seen below!

Current

40380 - A-00-L1
41105 - A-00-L2
41160 - A-00-L3
41216 - A-00-L4
41271 - A-00-L5
40381 - A-01-L1
41106 - A-01-L2
41161 - A-01-L3
41217 - A-01-L4
41272 - A-01-L5
40382 - A-02-L1
41107 - A-02-L2
41162 - A-02-L3
41218 - A-02-L4
41273 - A-02-L5

How I want it sorted

40380 - A-00-L1
40381 - A-01-L1
40382 - A-02-L1
41105 - A-00-L2
41106 - A-01-L2
41107 - A-02-L2
41160 - A-00-L3
41161 - A-01-L3
41162 - A-02-L3
41216 - A-00-L4
41217 - A-01-L4
41218 - A-02-L4
41271 - A-00-L5
41272 - A-01-L5
41273 - A-02-L5



Thank you,
Amos

[oeldere]

Select the total range.

Then sort on column A and after that sort on column B.

If this is not what you want, post an small excel file.

[FDibbins]

Select the range
go to Data/Sort and Filter/use the box option
Sort by Col A
Add Level
Sort by Col B

[AmosA]

This only works if the data in column A is in sequential order but my data is not. Any other ideas?

Thanks,
AA

[oeldere]

See my comment in #2.

If this is not what you want, post an small excel file.

[GeoffW283]

Split your "Location name" (col-B) into three subfields, each in their own column
Col-C will contain A, B, C etc,
Col-D will contain 0, 1, 2 etc,
Col-E will contain L1, L2, L3 etc,

AmosA.png

Now you can sort on col-C, then by c0l-E, then by col-D. This gets you the sort order you want.

Formulas - assuming fixed widths for the three subfields:
Col-C assuming a start in row-2

Please Login or Register  to view this content.

Col-D

Please Login or Register  to view this content.

Col-E

Please Login or Register  to view this content.

[TMS]

Or, you could select column B and use Text to Columns to get columns C, D and E.