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 check 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.

=LEFT(B5,SEARCH(" ", B5)-1)

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.

=MID(B5,SEARCH(" ",B5)+1,SEARCH(" ",B5,SEARCH(" ",B5)+1)-SEARCH(" ",B5)-1)

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.

=RIGHT(B5,LEN(B5) - SEARCH(" ", B5, SEARCH(" ", B5,1)+1))

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.

=TRIM(MID(SUBSTITUTE($B5," ",REPT(" ",999)),COLUMNS($C:C)*999-998,999))

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