Search code examples
excel

Excel - Several columns with and without data

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 |
Solution

  • 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