Sorting Blanks First texasdeputy 12/29/2008 3:54:06 PM 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.

Re: Sorting Blanks First Lars-Åke Aspelin <larske[ at ]REMOOOVE.telia.com> 12/29/2008 4:11:42 PM On Mon, 29 Dec 2008 07:54:06 -0800, texasdeputy <texasdeputy[ at ]discussions.microsoft.com> wrote: [Quoted Text] >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

RE: Sorting Blanks First Shane Devenshire 12/29/2008 9:54:05 PM 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: [Quoted Text] > 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.