-2

My spreadsheet has 20 x 100.000 (columns x lines) of data but with several empty columns, it was obtained from a PDF report. I would like to "group" all columns, therefore removing empty spaces in between, preferably without VBA codes. See example below.

Original spreadsheet:

Col.1 | Col.2 | Col.3 | Col.4 | Col.5    
12345 | empty | ABCD  | empty | 1A2B    
empty | empty | 45678 | empty | x1z2

Desired result:

Col.1 | Col.2 | Col.3 | Col.4 | Col.5 |  
12345 | ABCD  | 1A2B  | empty | empty |    
45678 | x1z2  | empty | empty | empty |
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
fabr
  • 23
  • 7
  • Programming is a difficult task and we have to use available tools. In this case, VBA was not an option and, even though, a solution was possible. Thanks Scott Craner – fabr Sep 08 '16 at 20:03

1 Answers1

2

Follow these steps:

  1. Highlight the entire range.

enter image description here

  1. Go to Find & Select from the HOME tab.
  2. Choose Go To Special.
  3. Choose Blanks and hit OK.

enter image description here

Result:

enter image description here

  1. Hit Ctrl--.
  2. Choose Shift cells left and hit OK.

enter image description here

Result:

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81