# 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.
`=LEFT(B5,SEARCH(" ", B5)-1)`

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

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