-1

I have a Google Sheet with multiple columns and some of the columns have empty cells at arbitrary locations. I want to 'condense' the columns, that is, simply shift upwards all cells in the column so that the column only consists of cells with values inside. All of my Google searches provide results for removing entire blank rows/columns, but do not relate to dealing with empty cells on the row/column level.

A demo Sheet can be found here.

  • 1
    Please add a brief description of your search/research efforts as is suggested in How to Ask and a link to a demo spreadsheet – Rubén Mar 7 at 19:23
  • Have you tried using the built-in Data>Sort Range tool? Select Column A, choose Data> Sort Range by Column A, sort A->Z; Select Column B, choose Data> Sort Range by Column B, sort A->Z; Select Column C choose Data> Sort Range by Column C, sort A->Z. – Tedinoz Mar 10 at 4:41
  • @Tedinoz But that would necessarily sort the values in the cells. I want them to maintain their current non-sorted order, as-is. – ChaimKut Mar 22 at 17:05
  • Your question has been asked and answered previously. Refer Removing only blank cells (not rows) in Google Sheets where @player0 provides both script and formula solutions. You should upvote one of them. FWIW, this was my google; it took 3 minutes to find the solution. – Tedinoz Mar 22 at 20:05
  • @Tedinoz Thanks, glad to know there's a welcoming and non-judgmental community here at Web Applications. 1) The StackOverflow question 'Removing only blank cells (not rows) in Google Sheets' does not appear in the first page of the Google results imgur.com/0Vq8wNW . 2) The Google search you linked to 'google sheets script remover empty cells' misspells the word 'remove'. 3) I am still interested in finding a simpler solution than scripting since that is certainly not ideal for the average user on 'Web Applications'. – ChaimKut Mar 24 at 13:05
0

use:

=ARRAYFORMULA({A1:C1; TRANSPOSE(SPLIT(TRANSPOSE(QUERY(A2:C,,999^99)), " "))})

Screenshot


Props: Player0

| improve this answer | |
  • As noted in the comments on Player0's answer, this solution a) doesn't work if the values in cells are more than 1 word b) it requires maintaining the data in two separate locations (the original data and the new data after the spaces are removed). Looks like we'll have to continue persisting to find a suitable solution. – ChaimKut Mar 26 at 10:47
  • This answer works perfectly with your sample data. Have you left some important information out of your question? – Tedinoz Mar 26 at 10:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.