Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting Blanks First
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting Blanks First
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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting data that contains blanks | Excel Discussion (Misc queries) | |||
sorting column with blanks | Excel Worksheet Functions | |||
Help with sorting blanks out of a list | Excel Discussion (Misc queries) | |||
Removing blanks / sorting - common methods not applicable | Excel Discussion (Misc queries) | |||
Sorting by looking for blanks | Excel Discussion (Misc queries) |