Sorting Blanks First
On Mon, 29 Dec 2008 07:54:06 -0800, texasdeputy
wrote:
I have a alphanumeric list as -
FM2130
FM613
FM3126
FM10
FM76
FM4120
I need it in an order as -
FM10
FM76
FM613
FM2130
FM3126
FM4120
It gets sorted as -
FM2130
FM3126
FM4120
FM10
FM76
FM613
The problem is that blanks need to be in a sort sequence first not last. How
do I fix this for all sorts.
Assuming that there are always two letters before the digits and that
your data is in column A from for 1 and down, you may try the
following formula in cell B1
=LEFT(A1,2)&SMALL(0+MID(A$1:A$6,3,9),ROW())
Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.
Change the 6 to fit the number of data that you have in column A,
You will now find the data sorted as you want in column B.
Hope this helps / Lars-Åke
|