How to Split in Excel (Text, Numbers, Cells & Columns)

Split in Excel means separating combined data from one cell into multiple cells, either across rows or columns.

In this tutorial, we will show you eight examples of splitting cells in Excel using different Excel features and functions.

For example, the below overview shows the summary of splitting first and last names, separating numbers from text, and splitting cells vertically across rows. Here, the C column shows the formula to execute all these methods to split in Excel.

This overview image shows the summary of this article. Please go through the whole article for more knowledge about splitting cells in Excel.

In this article, we will show you how to split in Excel while executing these practical examples below:

• Applying Excel features such as Text to Column Wizard and Flash Fill
• Using text functions such as the LEFT, SEARCH, RIGHT, LEN, MID, FIND, IF, and SUBSTITUTE functions
• Applying the TEXTSPLIT function to get the output both horizontally and vertically
• Separating numbers from text in Excel
• Splitting merged cells into multiple cells
• Separating cells ignoring blank cells, and applying the Excel VBA macro

⏷Apply Excel Features to Split in Excel
   ⏵Using Text to Column Wizard
   ⏵Applying the Flash Fill Option
⏷Use Text Functions to Split Names
   ⏵Splitting First Name and Last Name
   ⏵Splitting First Name, Middle Name, and Last Name
⏷Applying TEXTSPLIT Function to Split Cells Across Columns and Rows
   ⏵Split Cells Horizontally Across Columns
   ⏵Split Cells Vertically Across Rows
⏷Separate Numbers from Text in Excel
⏷Split Merged Cells into Multiple Columns
⏷Split Cells Ignoring Missing Values
⏷Split Columns Using Power Query
⏷Apply VBA Macro to Split Cells

1. How to Apply Excel Features to Split in Excel

Here, we will show you how to apply the Text to Column Wizard and Flash Fill features to split data in Excel. For example, the dataset below shows the Designation and Employee Name. We will use these two Excel features to split the Employee Name into First Name and Last Name.

1.1. Using Text to Column Wizard

Using the Text to Column feature is an easy way to split cells in Excel. We will use the Text to Column Wizard from the Data Ribbon.

For example, the dataset contains the employee name “Robert Henry.” Here we will split the text of Employee Name into two columns and get the first name, Robert, and the second name, Henry, in individual cells.

• Initially, select the cell range C6:C13 and go to the Data tab >> Data Tools >> Text to Columns option to get the Convert Text to Column Wizard dialog box.

• Once you get the Convert Text to Column Wizard dialog box, select Delimited and click on the Next button to complete the first step.

• Then, select Space >> click on the Next button to complete the second step.

• Finally, go to the third step and select General from the Column data format >> select cell D6 in the Destination field >> click on the Finish button to complete the total process.

Here we have selected D6 to get the output on this cell. Otherwise, this feature will split the data from the existing column.

• Now, you can see the final output after splitting cells using the Text to Column Wizard feature.

1.2. Applying Flash Fill Option

Applying the Text to Column Wizard is a time-consuming process. Also, to complete the process, you have to go through three steps. Instead, you can use the Flash Fill feature to separate data quickly and efficiently. For instance, the data contains the employee name “Robert Henry.” Here we will split the name using the Flash Fill feature and get the first name Robert and the second name Henry in individual cells.

• Write the first name in cell D6 and drag down the Fill handle to copy the name up to cell D13.
• Now, the Auto Fill option will pop up. Select Flash Fill from the drop-down menu of the Auto Fill option as below.
• Here, repeat the same process by writing the last name in cell E6.

2. How to Use Text Functions to Split Name

In this method, we will split data using formulas in Excel. Here, the dataset contains the employee named “Robert Henry.” Here, we will get the first name and last name using different functions. Then, we will show how to split names that have middle names as well. We will split the name “Robert Adam Henry” into first name, middle name, and last name.

2.1. Splitting First Name and Last Name

Using the LEFT and SEARCH functions, we will get the first names of the employees as the output. For example, if the dataset contains the employee name “Robert Henry” in cell C6, the output will be Robert using the below formula.

• Now, enter the formula in cell D6 and drag down the Fill Handle up to cell D13 to copy the formula.

The final output in cell D6 is Robert.

By using the RIGHT, LEN, and SEARCH functions combined, you can extract the last name. For example, the employee name in cell C6 is “Robert Henry.” If you apply this formula, then the output will be Henry.

• Now, enter the below formula in cell E6 and apply it to get the last name as an output.

Here, the final output in cell E6 is Henry.

There is another alternative option to get the first name and last name as output by using the FIND function instead of the SEARCH function. The working procedure of this formula is quite similar to the previous formula, and the output is also the same.

Select cell D6 and enter the below formula.

The output here is the first name of the employee, “Robert”.

Using the FIND function to get the last name as well is quite similar to the previous method. Here, this function will return the value from the right side of the string to another string.

• Therefore, enter the formula in E6 and complete the process to get the last name of the employee.

The last name of the employee is “Henry.”

2.2. Splitting First Name, Middle Name, and Last Name

In this part, we will separate the first name, middle name, and last name using Excel formulas. We have already shown how to split the first name from a combined string. So, we will use different functions, such as the IFERROR, MID, and SEARCH functions, to split the middle name. Then, we will use the IF, LEN, SUBSTITUTE, RIGHT, and SEARCH functions to split the last name.

For example, the dataset contains the employee name “Robert Adam Henry” in cell C6. The formula below will return Adam and Henry as the middle and last names, respectively.

• Therefore, enter the below formula in cell E6 to get the middle name as output.

The middle name is Adam.

• Enter the below formula in cell F6 to split the last name.

The final output, after splitting the last name, is Henry.

3. Applying TEXTSPLIT Function to Split Cells Across Columns and Rows

In this example, we will apply the TEXTSPLIT function to split cells across columns and rows. The TEXTSPLIT function splits text strings by using column and row delimiters. If your dataset contains more than one delimiter, then this function is appropriate to use.

The formula used before returns one value at a time, but this function separates each value into one string. For instance, if the dataset contains Robert Henry as the employee name, then the output will be Robert as the first name and Henry as the last name in individual cells. This function works like the Text to Column Wizard feature.

3.1 Split Cells Horizontally Across Columns

Here, we will split the string horizontally across columns using the TEXTSPLIT function.

• Apply the below formula to cell D6 and get both first and last names as output in individual cells.

Here the delimiter is space. You can change your delimiter according to your dataset.

Therefore, the output is Robert and Henry.

3.2 Split Cells Vertically Across Rows

Here, we will apply the same function, but this time we will split cells vertically across rows. There are some fruit names as products of a store in cell C6. We want to split these values vertically across rows. Apply the below formula to cell D9 and get the split data from cell C6.

Therefore, the output is Mango, Banana, Guava, Coconut, Apple, Lemon, Berry, and Cherry vertically.

4. How to Separate Numbers from Text in Excel

In this method, we will separate numbers and text in Excel. To execute this process, we will use the TEXTJOIN, MID, ROW, IFERROR, INDIRECT, and LEN functions. This formula identifies and separates the numbers from a string and returns the number value as the output. For example, your dataset contains an employee bio where the name, joining year, and experience are shown as “Robert Henry, Joined in 2022, First Job” and then the output will be 2022.

• Apply the below formula in cell D6 and separate numbers from the text.

Here, the output is 2022.

5. How to Split Merged Cells into Multiple Columns

In this example, we will split merged cells into multiple columns. Here we will use the TEXTSPLIT function to split the merged cells. For example, the employee bio is merged into columns C and D. Here the Employee Bio is “Robert Henry, Joined in 2022”. After using the TEXTSPLIT function, the output will be Robert Henry and Joined in 2022 in individual cells.

• Apply the formula in cell E6 and split the merged cells into multiple columns.

6. How to Split Text Ignoring Missing Values

In this method, we will split cells by ignoring missing values. Here, we will use the TEXTSPLIT function, where the delimiter is a comma. The fourth argument of the TEXTSPLIT function is ignore_empty. This argument is set to FALSE by default. So, this function doesn’t ignore the missing value by default and returns a blank cell for each missing value. So, we will set the ignore_empty  argument to TRUE and split the cells, ignoring the missing values.

For instance, the dataset contains the value “, Joined in 2022, First Job”. Here, the Name is missing. Therefore, the output will be Joined in 2022 in the first cell, ignoring the missing value. Apply the formula in cell D6 and split cells, ignoring blank cells.

Therefore, you can see the output is shown, ignoring the missing value.

7. How to Split Columns Using Power Query

In this method, we will split columns using Power Query. Power Query is an Excel feature that is a data transformation and data preparation engine. It helps to get and edit data from sources.

Here we will use the Split Column feature from Power Query to split the column.

• Initially, select cell range C6:C13 and go to the Data tab >> select Get Data from the toolbar.

• Then, select Get Data >> From File >> From Excel Workbook to get the Import Data dialog box.

• Now, select the required Excel file from the Import Data dialog box and click Import to open the Navigator window.

• After that, select the sheet from the Navigator tab and select Transform Data to transform the data.

• Therefore, select the column to split >> go to Split Column >> select By Delimiter to split the column by delimiter.

• Now, select Space from the select or enter delimiter option, and click OK to split the column by delimiter space.

• Finally, end the process by clicking the Close & Load option.

applying load and close to split in Excel using power query

• The final output will be like the following image: Here, the full name is split into columns B and C.

8. How to Apply VBA Macro to Split Cells

Excel VBA has a feature where you can get output within a blink of an eye. Go to the Developer tab >> Visual Basic and a Visual Basic window will open to enter the code. We will apply the VBA code to split first and last names. First, you need to write the code and then apply the code.

• Initially, enter the visual basic window and go to Insert >> Module to get the page to write the code.
• Now, write the below code to split cells in Excel using the VBA macro.

Sub SplitText()
Set ws = ThisWorkbook.Sheets("VBA")
lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
For Each cell In ws.Range("C6:C" & lastRow)
splitValues = Split(cell.Value, " ")
For i = 0 To UBound(splitValues)
If ws.Columns.Count < cell.Column + i + 1 Then
ws.Columns(cell.Column + i + 1).Insert
End If
cell.Offset(0, i + 1).Value = splitValues(i)
Next i
Next cell
End Sub

• Lastly, the final output will be similar to the one below.

Download Practice Workbook

This article describes everything about how to split cells in Excel. We have shown how to split merged cells and split ignoring missing values as well. We have also learned how to split the first name, middle name, and last name, and how to split numbers from a string when splitting numbers is necessary. Here, we have used different text functions such as LEFT, SEARCH, RIGHT, LEN, MID, FIND, SUBSTITUTE, TEXTSPLIT, TEXTJON, IFERROR, ROW, and INDIRECT functions to separate the numbers from texts. Besides, we have also used different Excel features, such as the Text to Column, Power Query, Excel VBA, and Flash Fill features.

Please leave a comment in the comment section if there is any query.

Split in Excel: Knowledge Hub

• Split Cells in Excel
• Split Column in Excel
• Split Date and Time in Excel

<< Go Back to Learn Excel