How to Split Data in Excel (5 Ways)

In different circumstances, we need to split our data into different pieces. These dissections of data are usually done by space, commas, or some other criteria. Splitting of these data can really help us to get what part of the data we need at a given time. 5 useful and easy methods of how to split data in Excel will discuss here in this article.

Download Practice Workbook

Download this practice workbook.

5 Ways to Split Data in Excel

To demonstrate how to Split data in Excel, we are going to use the following spreadsheet full form name of different persons in the Full Name column, and their names first part and the second part are also shown. How we get to these parts are explained and illustrated in different ways.

1. Text to Columns Features to Split Data in Excel

In this process, delimiters like space, tab, and commas separate the selected data in one or in more cells. Text to column feature is a great tool to split data in Excel

Steps

• First, select all the cells that you wish to split.
• Then go to Data > Text to Columns.

• After that, a new dialog box will open. from that box select delimited. And click Next.

• After clicking next, the next dialog box will appear.in that dialog box tick the Space option box, as we want to split the given data according to the position of space between words.

• Then in the next dialog box select General.
• Right below the Column data format box, there is a cell reference box Destination. In that box, you have to enter where your split data will be.
• Click Finish in the dialog box, after selecting the destination cells.

• Select your destination cells like below in the Destination box.

• After clicking Finish, you will notice that all of the names are now split into last and first names.

Read More: Excel Split Data into Columns by Comma (7 Methods)

2. Split Cells in Excel Using Formulas

The formula can be a handy tool while splitting Data in Excel. For example, using the TEXT function formula or TRIM / MID  we can easily and flexibly split different types of data.

2.1 Formula with the Text Functions

Steps

• We are going to use a different name dataset for this method. This dataset contains a middle name column compared to the previous dataset.

• Then we enter the following formula in Cell C5 :

• Then we select the fill handle and drag it to Cell 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:

• After pressing enter, the middle part of the Full name column is in the Cell D5

• After that, drag the fill handle button to Cell D10. It will split the other Full Names middle part.
• To split the last part of the Full name column, enter the following formula below:

• After pressing enter, you will see that the last part of the name in Cell B5 is split into Cell E5.

• Drag the fill handle button to the Cell E10. It will split the other full name’s last part in the Last Name column.

2.2 Use of Trim and Mid Functions to Split Data

Steps

• At first, you need to enter the following formula into Cell C5 :

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

• After that, select the fill handle button, and drag it horizontally to Cell E5.
• Then the Full Name column data in C5 will be completely split across three columns.

• Then select Cell C5:Cell E5, and then drag the fill handle down to Cell E10.

• After releasing the fill handlebar, you will observe that all of your Cell data are now split across three parts.

Read More: How to Split Data in One Excel Cell into Multiple Columns (5 Methods)

3. Split Data into Cells in Excel Using Flash Fill Feature

Steps

• At first, you need to fill up the first row of the dataset. That means you need to enter the split first name and last name in Cell C5 and Cell D5.

• After that, drag the corner handle to Cell C11 by pressing right click on the mouse.

• Then release the handle, upon releasing the handle, a new drop-down window will open. From that window, choose Flash Fill.

• Selecting the Flash Fill button will split the first part of the names in the name column as done in Cell C5.

• Repeat the same process for the Last Name column, this will split the last part of the names in the Full Name column.

Now all the names in the Full Name column are split into two parts.

Read More: How to Split Data from One Cell into Multiple Rows in Excel (3 Methods)

4. Split Cells and Text in Excel with Power Query

Using a powerful tool like Power Query in Excel, you can easily split the names in the Full Name column.

Steps

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

• Then a new Cell Reference box, in which you have to select the range of your table.

• After entering the Range, a whole new window will open, in which you have to remove the empty columns.

• After removing the columns, you need to Duplicate the Full Name column.

• Then right-click in your mouse, from the context menu go to Split Column > By Delimiter.

• A new window will open. In that window, select Space from the Select or enter delimiter drop-down menu. And select Each occurrence of the delimiter at Split at. Then click OK.

• After clicking OK, you will see that Full Names have been split into three separate columns.

• Change those column names to Middle Name, First Name, and Last Name. Then click Close and Load.

• After closing and loading the power tool. a new sheet will appear in the main workbook like this.

In this worksheet, you can clearly see that the names in the Full Name column are split into three separate parts based on the space between them.

5. Using VBA Macro to Split Data in Excel

A simple macro code in the VBA editor can solve all of the above problems quite easily.  At the same time Utilizing macros is quite hassle-free and time-saving.

Steps

• Launch the Visual Basic Editor from the Developer tab.
• pressing Alt + F11 on your keyboard can also activate the Visual Basic editor.

• After launching the Visual Basic editor, a new window will launch.
• In the new window click Insert, then click Module.

• Next, a white editor will open. In that editor, you need to write 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

• Upon writing the code, close both the Module and the VBA editor.
• From the View tab, click the Macros command, then select the View Macros option.

• Next, a new dialog box will open, from that dialog box, select the macro that you just created and click Run.

Upon clicking Run, you will see that all your names in the Full Name column are now split across three different parts.

Read More: Excel Macro to Split Data into Multiple Files (With Simple Steps)

Conclusion

To sum it up, the question “how to split data in excel“ can be answered in 6 principal ways. They are mainly by using formulas, using Text to Column function, deploying Power Query and another one is to run a small macro in VBA editor. The VBA process is less time-consuming and simplistic but requires prior VBA-related knowledge. Similarly, a Power query is also a very convenient tool but a bit time-consuming.

On the other hand, other methods don’t have such a requirement. The text to column method is by far the most convenient and easy to use among all of them. For this problem, a practice workbook is available to download where you can practice and get used to these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.

Related Articles

• How to Split Data into Equal Groups in Excel (3 Methods)
• How to Split Comma Separated Values into Rows or Columns in Excel
• Excel Macro to Split a Cell into Multiple Rows (With Easy Steps)
• How to Split Data into Multiple Worksheets in Excel