Revision history [back]

You can split an Excel file with a single column containing many records by using the Text-to-Columns feature. Here's how:

1. Open your Excel file and select the column that you want to split.
2. Click on the Data tab and select Text to Columns.
3. In the Text to Columns Wizard, select Delimited and click Next.
4. Choose the delimiter that separates your entries (e.g. comma, semicolon, tab), and preview the results in the Data preview section.
5. Click Next and select the data format for each column if needed.
6. Click Finish to split the column into multiple columns.

Alternatively, if you want to split the column into separate worksheets by each record, you can use the Macro recorder:

1. Press Alt+F11 to open the Visual Basic Editor.
2. Click on Insert > Module to create a new module.

3. Copy and paste the following macro code:

   Sub SplitByRecord()

   Dim lastRow As Long, i As Long, j As Long Dim currentRecord As String Dim currentSheet As Worksheet

   'Get the last row in the current column lastRow = Cells(Rows.Count, 1).End(xlUp).Row

   'Loop through each row and split into separate sheets For i = 1 To lastRow 'Get the current record currentRecord = Cells(i, 1).Value

   'Add a new sheet for the current record
   Set currentSheet = ThisWorkbook.Sheets.Add(After:= _
                    ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
   currentSheet.Name = currentRecord
   
   'Copy the record to the new sheet
   Range("A" & i).Copy currentSheet.Range("A1")
   

   Next i

   End Sub

4. Press F5 to run the macro.

This macro will create a new sheet for each record in your column and copy the record to the new sheet.