How do I split multiple lines of text into seperate columns within Excel?

There are a couple different methods for splitting the text, 2 of the most common are listed below.

1. Open Your Excel Document
2. Highlight the column you want to split the text on.
3. Click on the Data tab, then Text to Columns.
4. Choose a delimited format, choose Other as the delimiter then press CTRL and J on the keyboard at the same time. This makes your delimited a line break (similar to ALT + ENTER) in excel so that each line break that is found is used as the delimiter in your data.
5. Follow the rest of the steps through to verify your data before completing the Text to Columns split.

Sometimes using Data | Text to Columns does not function properly depending on the type of data, how the line breaks were created, etc.

1. Open Your Excel Document.
2. Create 4-5 blank columns to the right of the column you want to split out into multiple columns. These are needed because the data from your multiple line cell will be inserted into these fields. If you do not insert these fields the data in the fields to the right of your primary cell will be overwritten.
3. Click on View | Macros | View Macros
4. Type SplitText into Macro Name field.

    Sub splitText()
    'splits Text active cell using ALT+10 char as separator
    Dim splitVals As Variant
    Dim totalVals As Long
    Dim i As Integer
 
    For i = 1 To 1000
      splitVals = Split(ActiveCell.Value, Chr(10))
      totalVals = UBound(splitVals)
      Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), Cells(ActiveCell.Row, ActiveCell.Column + 1 + totalVals)).Value = splitVals
      ActiveCell.Offset(1, 0).Activate
    Next i
    End Sub

1. Click the Create button
2. Highlight all the text in the VBA editor and delete it. Be careful not to delete other macros you've created.
3. Close the VBA editor once you have pasted the code.
4. To run the macro highlight the first cell that you want to split the data on and Click View | Macros | View Macro then click on SplitText in your macro list and click Run. This macro does the first 1000 lines, just repeat the process on the 1001 record by highlighting it and running the macro again.

• Entered : 06/2012