Excel Sort

A

abs

Using Excel 2002 and have a list as follows:
1B
1H
10A
11F
100H
102H

When I hit sort, the list sorts as:
100H
102H
10A
11F
1B
1H

I want it to sort:
1B
1H
10A
11F
100H
102H

Can that be done easily?
 
M

Martin P

Copy to Word.
In Word:
Convert table to text (separate text with paragraph marks)
Edit, Replace. Wildcards enabled. Replace ([0-9])([A-Z]) with \1^t\2
Convert text to table (separate text at tabs)
Sort by Column 1
You can use either Word or Excel to put the two parts together.
 
D

Dave Peterson

Always a bunch of numbers followed by a single alphabetic character?

If yes, then insert two helper columns:

(with the data in column A)

in B1:
=--left(a1,len(a1)-1)

in C1:
=right(a1,1)

And drag down your range.

Sort by these two helper columns.

(Delete them when you're done (if you want to).)
 
Top