×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Sorting combinations of letters and numbers

## Sorting combinations of letters and numbers

(OP)
I have a list I want to sort in the following way:

original
--------
200
102A
300A
305B
100
101

WANT
-------
100
101
102A
200
300A
305B

unfortunately excel likes to sort all numbers first and all letters second.  So I get the following undesired result:

EXCEL'S RESULT
--------------
100
101
200
102A
300A
305B

Any Ideas?

### RE: Sorting combinations of letters and numbers

Remove the letter, if occurs, by using the LEN, RIGHT, and/or  LEFT functions (I don't know if you always have 3-digits of numbers, so can't say exactly which function will work best).
See Thread 770-131895, sort the numbers list. Do a second sort of the letters "suffix", and use CONCATENATE to restore the letter "suffix" to the numerical portion.

### RE: Sorting combinations of letters and numbers

(OP)
Thank you SacreBleu.  However, that thread is on the automatic sorting of NUMBERED lists only.

My problem is that I need to sort both Numbers and Numbers+letters in a way that is not Excel's default way.

### RE: Sorting combinations of letters and numbers

One way but tedious :
In an adajacent column use =""&A1 for each row.
Range Value the new column
Now sort new column and when asked select option sort numbers and numbers stored as text separately.

### RE: Sorting combinations of letters and numbers

(OP)
Ok found a solution that is not elligant, but works.  I found the solution on the follow web site under "Sorting mixed numbers and alpha, numeric prefix (#mixed)"

http://www.mvps.org/dmcritchie/excel/sorting.htm

Only I made a quick formula rather than their CellValue formula.

=IF(B2="",A2,VALUE(LEFT(A2,3)))

Where column 'A' is my values, 'B' is my dummy row for sorting.  Then I just do a multiple sort with column B first and A second.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!