How to sort numbers+space+letters combination by letters? 1

[calpolymis]

Hello. I need to sort a column with combination of numbers(sales order#,) a space, and letters(category ID.) The current column is sorted by numbers and looks like this:

81000 WARRANTY
81001 WARRANTY
81002-A DLR
81003 DLR
81004 EXPORT
81005-A EXPORT

I need to sort the column in the following way by Category ID:
81002-A DLR
81003 DLR
81004 EXPORT
81005-B EXPORT
81000 WARRANTY
81001 WARRANTY

Or if not too much complicated, in this way(preferred):
DLR 81002-A
DLR 81003
EXPORT 81004
EXPORT 81005-B
WARRANTY 81000
WARRANTY 81001

How can I acheive this? Thanks for your comments in advance.

 

[dhookom]

Is this Access or Excel or something else?
You might want to check Help on Instr() for Access or Find() for Excel. Combine this with Mid() or Left() or Right() to create the expression you can sort by.

Duane
Hook'D on Access
MS Access MVP

 

[calpolymis]

This is MS excel. I'm not very familiar with the functions you mentioned-I believe this task could be acheived by writing a short function, but I've never written a function myself. I need someone's help on this. Thank you.

 

[SkipVought]

If the data can be parsed into 2 columns, just use Data > Text to columns > DELIMITED (using the SPACE)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Scott24x7]

calpoly,

In Excel, in an unused field (I'll assume your values are in the A column at this point, and they start in the 2nd cell). So pick any empty column, and copy into the cell to the right this:

=RIGHT(A2,LEN(A2)-FIND(" ",A2,1))&" "&LEFT(A2,FIND(" ",A2,1))

Once you have it copied into that cell, just drag it down the other cells using the small square on the corner of the cell when you have the cell selected. It will change all the column and cell values for you.

Now you have a column in the data that looks like your preferred way. Then just select all rows and columns, and sort the table on that column.



Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[calpolymis]

Scott's solution worked like a charm! Thanks for other commentors-SkipVough's instruction worked too, but I could not sort the data correctly in the number field (I think XXXXX-A format is not sorted with other numbers, so I would probably need another tweak to handle that.)

I will study Scott's function for learning. Thanks all of you and have a wonderful day.

Jean

 

[SkipVought]

I think XXXXX-A format is not sorted with other numbers, so I would probably need another tweak to handle that.

Parse that column as TEXT.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[calpolymis]

Before I posted my reply, I tried parsing as a text-It didn't work at that time, but it suddenly works now! I guess my Excel 2002 had a little functional glitch or something. Anyways, it is so nice to learn this kind of advanced functionality that Excel can offer. Thanks again~