How to Split Data in Excel – 5 Methods

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

5 Ways to Split Data in Excel


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.

5 Ways to Split Data in Excel

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

Text to Columns Features. to Split Data in excel

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.

Split Cells in Excel Using Formulas

  • Enter the following formula in C5.
=LEFT(B5,SEARCH(" ", B5)-1)

Split Cells in Excel Using Formulas

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

Split Cells in Excel Using Formulas

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

Split Cells in Excel Using Formulas

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

Trim and Mid Function Formula to Split Data

  • Select the fill handle and drag it to E5.

The Full Name column data in C5 will be split in three columns.

Trim and Mid Function Formula to Split Data

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

Trim and Mid Function Formula to Split Data

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.

Split Data in Cells by Excel Using Flash Fill Feature

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

Split Data in Cells by Excel Using Flash Fill Feature

Split Data in Cells by Excel Using Flash Fill Feature

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

Split Data into Cells by Excel Using Flash Fill Feature

Repeat the same process for the Last Name column.

Split Data into Cells by Excel Using Flash Fill Feature

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.

Split Cells and Text in Excel with Power Query

  • In Create Table, select the range.

Split Cells and Text in Excel with Power Query

 

  • In the new window, choose remove columns.

Split Cells and Text in Excel with Power Query

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

Split Cells and Text in Excel with Power Query

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

Split Cells and Text in Excel with Power Query

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.

Using VBA Macro to Split Data in Excel

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

Using VBA Macro to Split Data in Excel

  • 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 split in three columns.

Using VBA Macro to Split Data in Excel


Download Practice Workbook

Download this practice workbook.

Get FREE Advanced Excel Exercises with Solutions!
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo