Getting rid of the blanks in a spreadsheet column or row, and more

Note: As Karl pointed out in a comment, this is actually a complex solution. It is a good solution for two or more columns, but if you have one column as shown here, the simplest approach is to just sort the column and the blanks and cells with one space in them will be grouped together. Delete those cells and you get the same result.

I'm going to a big blues dance event this weekend. The people signed up are listed on EventBrite, so since I wanted a list of people coming, I copied the list and pasted it into OpenOffice. But when I pasted the list to a spreadsheet, I got a bunch of blank cells. I want to have a nice singlespaced list of the names.

 

 By the way, Edit > Paste Special, Skip Empty Cells, won't do what I want. It's a badly phrased option and has to do with whether you overwrite when pasting.

Anyway--so to remove the empty cells I selected the column and chose Data > Filter > Standard Filter. I made sure that the Name column was selected, left = selected, and selected Not Empty.

 

I clicked OK and....voila. Voi not, that is. 

That pesky EventBrite site puts in a space, just a single space, in some but not all of the empty lines. Why? Well, why not. I discovered this by clicking in the remaining seemingly empty cells, and yep, there was a blank space.

 

So now I need to screen out not only stuff that's not empty, but stuff that doesn't have a space as the only content. That's a little hard....and I don't want to search and replace to remove spaces, since then I'll end up with TrentPrice instead of Trent Price.

I went back to Data > Filter > Standard Filter and filtered out everything that STARTS with a single space. 

 

And that worked great.

 

Comments

[Karl Erik Jessen]

As long as you have only one column of data I think the simplest solution would be to select all the names on your list - including intermediate "empty" cells - and then sort this selection. The "empty" cells will then be at the end of the selection and can then be deleted or just ignored

Of cause if your data consists of multiple columns and especially if data regarding one entity is spread over more than one row you can not sort things out. Then you will have to filter like suggested above

[Solveig]

Thanks Karl! You are absolutely right. I got caught up in the Ignore Blanks option but of course your approach is far simpler.