How to Split Data in Excel (5 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

5 Ways to Split Data in Excel


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.

5 Ways to Split Data in Excel

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

Text to Columns Features. to Split Data in excel

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

Split Cells in Excel Using Formulas

  • Then we enter the following formula in Cell C5 :

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

  • Then we select the fill handle and drag it to Cell C10.

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

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

Split Cells in Excel Using Formulas

 

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

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

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

Split Cells in Excel Using Formulas

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

=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

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

Trim and Mid Function Formula to Split Data

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

Trim and Mid Function Formula to Split Data

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

Split Data in Cells by Excel Using Flash Fill Feature

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

Split Data in Cells by Excel Using Flash Fill Feature

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

Split Data in Cells by Excel Using Flash Fill Feature

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

Split Data into Cells by Excel Using Flash Fill Feature

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

Split Data into Cells by Excel Using Flash Fill Feature

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.

Split Cells and Text in Excel with Power Query

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

Split Cells and Text in Excel with Power Query

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

Split Cells and Text in Excel with Power Query

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

Split Cells and Text in Excel with Power Query

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

Split Cells and Text in Excel with Power Query

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

Using VBA Macro to Split Data in Excel

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

Using VBA Macro to Split Data in Excel

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

Using VBA Macro to Split Data in Excel

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

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo