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?
garryshapeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

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
0
garryshapeAuthor Commented:
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
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

garryshapeAuthor Commented:
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
0
barry houdiniCommented:
Try using a helper column. In row 4 of a blank column use this formula copied down
=AND(A4<>"",ISERR(LEFT(A4)+0))
Now sort your data using the new column as the first "key" (descending) and column A as the second.....
regards, barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
barry houdiniCommented:
....here's that technique applied to your sample.....
regards, barry

25772838.xls
0
garryshapeAuthor Commented:
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
0
garryshapeAuthor Commented:
I mean I pasted the formula into E4 not A4...
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
garryshape,

the formula should point to the column where your data is and the row number in the formula references must be the current row.

So, if your data starts in A4, this needs to go into E4

=AND(A4<>"",ISERR(LEFT(A4)+0))

then copy the formula down to the last row of data.

Select your data starting from A4 and include the values in column E. Click the Sort button on the Data ribbon and enter two sort levels

by column E  - largest to smallest
by column A - A - Z

cheers, teylyn
0
garryshapeAuthor Commented:
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?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.