Excel – Split long column into multiple columns by empty cell

microsoft excel

So I have data, that once imported into excel looks like this:

1
2
3
4

A
B
C
D

!
@
#
$

I want the data to look like this:

1 A !
2 B @
3 C #
4 D $

All the data is in one column, and each new set of data is offset by an empty cell. All I want to do is be able to split that one column into multiple columns, creating a new column at every empty cell. Each time I record data, there will be an equal number of data entries in each set, but they are not of the same type, and between recordings there can be different numbers of entries in each set. Basically, all I want to have happen is excel goes down the column, and when it finds an empty cell, it copies all remaining data over to the next column and then looks for the next empty cell, at which point it will do the same thing until there are 2 empty cells in a row.

Searching how to do this is extremely annoying because I keep getting text to column results, which isn't what I want.

Thanks in advance.

Best Answer

  • I would recommmend preprocessing the data before importing them into Excel.

    However I created procedure in VBA that does what you want:

    To install the macro

    Open Excel -> Alt+F11 -> Insert -> Module -> paste the following code -> Ctrl+S -> and select 'Excel Macro-Enabled Workbook (*.xlsm)' from the drop-down list

    Sub ToManyColumns()
        Dim firstCellRow As Long
        firstCellRow = 1              'change this if you don't want to start at A1
        Dim firstCellColumn As Long
        firstCellColumn = 1           'change this if you don't want to start at A1
        
        Application.ScreenUpdating = False
        ActiveSheet.Cells(firstCellRow, firstCellColumn).Activate
        Dim column As Long
        column = firstCellColumn
        Dim startIndex As Long
        Dim endIndex As Long
        Dim lastRow As Long
        lastRow = firstCellRow
        
        Do While True
            'find the range to copy
            startIndex = ActiveCell.row
            Do While ActiveCell.Value <> ""
                endIndex = ActiveCell.row
                ActiveCell.Offset(1).Activate
            Loop
            
            lastRow = ActiveCell.row
            
            Range(Cells(startIndex, firstCellColumn), Cells(endIndex, firstCellColumn)).Select
            Selection.Copy
            Cells(firstCellRow, column).Select
            Selection.PasteSpecial Paste:=xlPasteValues
            
            'get back to last rowIndex
            Cells(lastRow, firstCellColumn).Activate
            ActiveCell.Offset(1).Activate
            
            If ActiveCell.Value = "" Then Exit Do
            
            column = column + 1
        Loop
        
        'cleanUp -------------------------------------------
        Dim deleteFrom As Long
        Dim deleteTo As Long
        deleteTo = ActiveCell.row
        
        ActiveSheet.Cells(firstCellRow, firstCellColumn).Activate
        Do While ActiveCell.Value <> ""
            ActiveCell.Offset(1).Activate
        Loop
        deleteFrom = ActiveCell.row
        
        Range(Cells(deleteFrom, firstCellColumn), Cells(deleteTo, firstCellColumn)).Select
        Selection.ClearContents
        
        ActiveSheet.Cells(firstCellRow, firstCellColumn).Activate
        'cleanUp -------------------------------------------
        
        Application.ScreenUpdating = True
    End Sub
    

    To run the macro:

    1. Make sure that you're in the worksheet you want to be! And click anywhere into the worksheet! (Because this macro runs in the Activated Worksheet)
    2. Alt+F11 -> Click anywhere into the code -> press F5

    You can also create a button in the worksheet and assign the macro to it - it's more user-friendly and you don't have to check that you're in the right worksheet.

    Notes

    If you don't want the macro to start at cell A1 (for example: start at different column), then change the numbers on 3rd and 5th lines.

    For this purpose it's better to preprocess the data than use macros...