Sorting Blanks First

  • Thread starter Thread starter texasdeputy
  • Start date Start date
T

texasdeputy

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.
 
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.
Click to expand...


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
 
Hi,

I can't reproduce your results

You say you get:

FM2130
FM3126
FM4120
FM10
FM76
FM613

I get:

FM10
FM2130
FM3126
FM4120
FM613
FM76

To get a sort based on the numbers, assuming each is preceeded by to text
characters, create a dummy column with the formula:

=--MID(A2,3,10)

This assumes the entries start in A1. Copy the formula down and then sort
on this column.
 
Back
Top