How to Split Data in Excel – 5 Methods

The  following dataset showcases full names and first and second names.

Method 1 – Using Text to Columns Features to Split Data in Excel

Steps

• Select the cells you want to split.
• Go to Data > Text to Columns.

• A dialog box will open. Select delimited and click Next.

• In the new dialog box tick Space.

• In the next dialog box select General.
• In Column data format, choose Destination. Enter cell reference to split data.
• Select the destination cells and click Finish.

• These are your destination cells:

• All names are split into last and first names.

2. Splitting Cells in Excel Using Formulas

2.1 Formula with Text Functions

Steps:

• This is a new dataset, containing a middle name column.

• Enter the following formula in C5.

• Select the Fill Handle and drag it to C10.

• This formula will split the first part of the Full Name column.
• To split the middle part of the First Name column, enter the following formula and press Enter.

• The middle name is split in D5.

• Drag the fill handle to D10.
• To split the last part of the Full Name column, enter the following formula.

• Press enter and the last name in B5 is split into E5.

Drag the fill handle button to E10.

2.2 Using the Trim and Mid Functions to Split Data

Steps:

• Enter the following formula in C5.

• This formula will split the first part of the Full Name in the First Name column.

• Select the fill handle and drag it to E5.
• The Full Name column data in C5 will be split in three columns.

• Select range C5:E5 and drag the fill handle to E10.

This is the output.

Method 3 – Splitting Data into Cells Using the Flash Fill Feature in Excel

Steps:

• Enter the split first name and last name in C5 and D5.

• Right-click to drag the corner handle to C11.

• Choose Flash Fill. from the drop-down menu.

• It will split the first part of the names in the name column.

• Repeat the same process for the Last Name column.

This is the output.

Method 4 – Splitting Cells and Text in Excel with the Power Query

Steps:

• Select any cell inside the table, and go to Data > From Table/Range.

• In Create Table, select the range.

• In the new window, choose remove columns.

• Right-click > Click the Duplicate Column option to duplicate the Full Name column.

• Select Full Name – Copy column and right-click.
• Go to Split Column > By Delimiter.

• A new window will open.
• In Select or enter delimiter, select Space.
• Choose Each occurrence of the delimiter in Split at and click OK.

• The full names were split into three columns.

• Change the column names to First Name, Middle Name, and Last Name.
• Click Close & Load.

• This will be the output.

Method 5 – Using a VBA Macro to Split Data in Excel

Steps:

• Go to the Developer tab and choose Visual Basic or press Alt + F11.

• In the new window click Insert, and then Module.

• The Editor Window will open. Enter the following code.

Sub Split_Data()

Dim My_Array() As String, Column As Long, x As Variant

For m = 5 To 11
    My_Array = Split(Cells(m, 2), " ")
    Column = 3
    For Each x In My_Array
        Cells(m, Column) = x
        Column = Column + 1
    Next x
Next m

End Sub

• Close the Module.
• In the View tab, click Macros and select View Macros.

• In the new dialog box, select the Macro you created and click Run.

• All names in the Full Name column are now split in three columns.

Download Practice Workbook

Download this practice workbook.