Sort alphanumerical column with letters first
I have a column A listed with part numbers that are alphanumeric however I'm trying to sort them so that parts beginning with letters are first and those starting with numbers are at the end of the list.
A Microsoft article on alpha-numeric sorting suggested converting values of the cells into text, but it still sorts by numbers.
I have 3 header rows and there's about 8 other columns that need to stay with the data in Column A.
Is this possible?
A Microsoft article on alpha-numeric sorting suggested converting values of the cells into text, but it still sorts by numbers.
I have 3 header rows and there's about 8 other columns that need to stay with the data in Column A.
Is this possible?
ASKER
How would I try doing that on only certain rows?
There are 4 "header' rows and the last row is a totals one, so I wouldn't want those to sort.
Only certain rows in column a and the data next to them
There are 4 "header' rows and the last row is a totals one, so I wouldn't want those to sort.
Only certain rows in column a and the data next to them
I don't understand.
You want to sort the data in columns A to E, for example, using column A for the sort and moving the data in B to E according to the sort order of column A. If you have headers and your data starts in row 4, you can select A4 to E1000 and click the sort button on the data tab, then use the Order drop down to specify your list.
If this does not help, post a small data sample that illustrates your data layout.
cheers, teylyn
You want to sort the data in columns A to E, for example, using column A for the sort and moving the data in B to E according to the sort order of column A. If you have headers and your data starts in row 4, you can select A4 to E1000 and click the sort button on the data tab, then use the Order drop down to specify your list.
If this does not help, post a small data sample that illustrates your data layout.
cheers, teylyn
ASKER
Ok, I understand now about selection.
As for sorting, I don't think I'm doing it right.
I created a custom list like this but it still sorts the cells in column a that begin with numbers at the top
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
0
1
2
3
4
5
6
7
8
9
excel-sample.xlsx
As for sorting, I don't think I'm doing it right.
I created a custom list like this but it still sorts the cells in column a that begin with numbers at the top
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
0
1
2
3
4
5
6
7
8
9
excel-sample.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Barry, sorry but I am new to helper columns. I have a couple questions to help me understand it.
Does the formula point to A4 for a specific reason?
I pasted the formula into A4 in a new blank column (column E), and it says "TRUE".
When I go to Sort then, how do I choose a first "key"? Is that the same thing as "level"? If so, I don't see "
"descending" but I do see "largest to smallest" and "smallest to largest"....
Hope I'm on the right page
Does the formula point to A4 for a specific reason?
I pasted the formula into A4 in a new blank column (column E), and it says "TRUE".
When I go to Sort then, how do I choose a first "key"? Is that the same thing as "level"? If so, I don't see "
"descending" but I do see "largest to smallest" and "smallest to largest"....
Hope I'm on the right page
ASKER
I mean I pasted the formula into E4 not A4...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks very much, that worked perfectly for me.
Can you provide some learning resources to where I can learn more tricks like this one?
Would the Lynda Excel training videos cover this type of stuff?
Or are these functions more for advanced users?
Can you provide some learning resources to where I can learn more tricks like this one?
Would the Lynda Excel training videos cover this type of stuff?
Or are these functions more for advanced users?
you can set up a custom list and then sort by that.
Click the Office button - Options - Advanced and scroll down to the General section to click Edit Custom Lists
Here you can create a new list that starts with
a
b
c
etc
then
A
B
C
etc
then
0
1
2
etc.
and ends with the numbers. Make sure to also include the Capital letters in the list, either before or after the lower case,
Now when you sort, you can choose "Custom List" in the Sort dialog and select your custom list.
Does that help?
cheers, teylyn