sorting columns so blanks are first

jkdyer · Apr 15th, 2002, 03:43 PM

Excel help says that in sorting blanks are sorted last. What is the simplest(one-click)/best(most portable)/easiest way to have the sort blanks either ascending or descending (user's choice!)?

Jay Petrulis · Apr 15th, 2002, 03:58 PM

Hi,

I don't think it is easy to sort blanks to the top.

One way to do so would be to highlight the range in question and Edit>Goto>Special>Blanks>OK

Then, in the formula bar type ="" and hit Ctrl+Enter
(this is specific: not just Enter or Ctrl+Shift+Enter)

Then sort your data. If there is numeric data in the range, it will sort to the top, then the blanks, then the text. Highlight the blanks and numbers only and sort descending to get the sequence blanks, numbers, text.

There may be easier ways, but this isn't as simple as it sounds, I think. I hope someone will prove me wrong.

Regards,
Jay

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 15th, 2002, 03:43 PM	#1
jkdyer New Member Join Date: Apr 2002 Posts: 1	Excel help says that in sorting blanks are sorted last. What is the simplest(one-click)/best(most portable)/easiest way to have the sort blanks either ascending or descending (user's choice!)?

Apr 15th, 2002, 03:58 PM	#2
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Hi, I don't think it is easy to sort blanks to the top. One way to do so would be to highlight the range in question and Edit>Goto>Special>Blanks>OK Then, in the formula bar type ="" and hit Ctrl+Enter (this is specific: not just Enter or Ctrl+Shift+Enter) Then sort your data. If there is numeric data in the range, it will sort to the top, then the blanks, then the text. Highlight the blanks and numbers only and sort descending to get the sequence blanks, numbers, text. There may be easier ways, but this isn't as simple as it sounds, I think. I hope someone will prove me wrong. Regards, Jay