alphanumeric sort

J

JLW

I would like to sort values for numbered connectors such as C1, C2, C3,
....... C200. However, excel is sorting it as C1, C100, C11, C123, etc. How
do I change this???
 
D

Daniel CHEN

I do not think there is any direct way to do it.
You can use code to separate the text part and numerical part and then sort
them.

Something like this:
Suppose all your values in Column A, then
in Column B - extract the text part, "C"
in Column C - extract the numerical part, "2", "22", etc.
Sort by Column B first then Column C.

--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
Free Excel / VBA Training Materials is Available for Download
=================================
 
H

Hans Knudsen

If you always have only one "C" at the left you might want to try this array formula:
(assume your data is in A1:A6)
="C"&SMALL((RIGHT($A$1:$A$6,LEN($A$1:$A$6)-1))*1,ROW(A1))
(hold down Ctrl and Shift while pressing Enter)

Regards
Hans
 
Top