Splitting Text in Excel (8 Useful Applications)

Working on different types of text is very common in excel. But sometimes we need to split them for the need of better understanding. In this article, we will learn about splitting text in Excel with 8 applications.

Get this sample file and try it yourself.

8 Useful Applications for Splitting Text in Excel

For illustration, here is a dataset with the information of the Name & Address of 5 persons living in different countries.

Now let’s follow the examples below to split text in this dataset.

1. Use Column Wizard to Split Text in Excel

This first method will guide you on splitting text with the help of Text to Column Wizard identifying the Delimiter. Let’s check the process below:

• In the beginning, go to the Data tab and select Text to Columns under the Data Tools group.

• Then, the option Delimited in the first step of the Text to Column Wizard window and press Next.

• In the second step, select the Delimiters as Comma and press Next.

• In the last step, provide the Destination as cell C5 and press on Finish.

• That’s it, we have our splitted text as the following image:

2. Split Text with Flash Fill in Excel

Flash Fill is a good supplement to Column Wizard for splitting text in excel. Following is the process:

• First, type the first part of your text in cell C5.

• Then, go to the Data tab and select Flash Fill under the Data Tools group.

• Accordingly, you will see that cell range C6:C9 is automatically filled with the first part text from each of its sources.

• Follow the similar procedure and you will get the final output.

3. Combine LEFT, RIGHT, FIND & LEN Functions for Splitting Text

Another helpful method to split text is combining the LEFT, RIGHT, FIND & LEN functions in excel. Let’s see how it works.

• First, insert this formula in cell C5.

• Then, press Enter.
• Therefore, use the AutoFill tool and you will the result below:

Here, we applied the LEFT function to fetch data from the leftmost side of the cell. Following, we used the FIND function to search for that text position in cell B5.

• Now, insert this formula in cell D5.

• Next, hit Enter.
• Similarly, use the AutoFill tool to get splitted text in cell range D5:D9.

Here, we applied the RIGHT function to extract text from the right part of cell B5. Then, we used the LEN function to define the length of the text string. Lastly, applied the FIND function to search for the specific text string.

4. Insert TRANSPOSE Function for Splitting

The TRANSPOSE function is also very helpful for splitting text. Following is the process:

• Firstly, insert this formula in cell C5.

• Secondly, press Enter.
• Following, you will see that the text is automatically divided into cells C5, D5 and E5.

Here, we used the TRANSPOSE function to create an array for splitting the text. Then, we insert the FILTERXML function for extracting individual items from cell B5. Lately, we used the SUBSTITUTE function to replace text based on the dataset.

• Finally, use the Fill Handle tool to the following cells.

=FILTERXML(""&SUBSTITUTE(B3,","")&"", "//s")

5. Apply Excel VBA to Split Text

Excel VBA is an excellent solution to split text in excel. Let’s check the simple steps below:

• In the beginning, go to the Developer tab and select Visual Basic from the Code group.

• Then, select Module from the Insert section.

• After that, insert this code on the blank page.

Sub SplitCellText()
Dim MyAry() As String, Cnt As Long, j As Variant
For n = 5 To 9
    MyAry = Split(Cells(n, 2), ",")
    Cnt = 3
    For Each j In MyAry
        Cells(n, Cnt) = j
        Cnt = Cnt + 1
    Next j
Next n
End Sub

• Following, click on Run Sub or press F5 on your keyboard.

• Next, click on Run in the Macros window.

• Finally, you have successfully done the splitting of text in excel.

6. Use Power Query for Splitting Text in Excel

Another useful method to split text is to use the Power Query in excel. This tool helps to import and connect to an external workbook and reshape them according to preference. Carefully follow the process below:

• First, go to the Data tab and select Get Data.

• Then, select From File and then From Excel Workbook under the context menu.

• Next, select the Power Query worksheet in the list of the Navigator.
• Then, press Transform Data.

• After that, in the window, go to the Transform bar and click on Split Column.

• Thereafter, select By Delimiter from the drop-down.

• Following, you will be directed to the Split Column by Delimiter dialogue box.
• Here, select the delimiter as Comma.
• Along with it, select the option Each occurrence of the delimiter to Split at.

• Next, press OK.
• Lastly, press on Close & Load.

• Finally, you will see a new worksheet where the tests are splitted

• You can modify this table according to your preference afterward.

7. Text Splitting with TEXTBEFORE & TEXTAFTER Functions

In this last method, we will describe the process using the TEXTBEFORE and TEXTAFTER functions in a new dataset. Here, it shows the information of 5 persons’ full names in cell range B5:B9. Let’s check the steps below:

• Firstly, insert this formula in cell C5 and hit Enter.

Here, the TEXBEFORE function is used to return the text in cell B5 before any delimiter.

• Next, apply this formula in cell D5 and press Enter.

Here, the IFERROR function is applied to return a value in the formula that specifies error. Following we used the TEXTBEFORE and TEXTAFTER functions to return the text in cell B5 before and after any delimiter respectively.

• Lastly, apply this formula in cell E5.

Here, the TEXAFTER function is used to return the text in cell B5 after any delimiter.

• Now, you will see the output of cell B5.

• Lastly, use the Fill Handle tool and get the final output.

8. Split Text from a Line Break in Excel

In this section, we will discuss a new aspect of splitting text. Here, we will see the process of splitting text from a line break. Let’s check the process below:

• First, insert this formula in cell C5.

Here, first, we use the LEFT function to extract the left side value. Then applied the SEARCH function to return the position as a number in the text string. Lastly, we used the CHAR function to return a character specified by a number in cell B5.

• Then, apply this formula in cell D5.

Here, the MID function is used to fetch the value from the middle line in cell B5. Then applied the SEARCH function to return the position in the text string. Lastly, we used the CHAR function to return a character specified by a number.

• Lastly, insert this one in cell E5.

Here, first, we use the RIGHT function to extract the right side value. Then applied the SEARCH function to return the position as a number in the text string and the LEN function to determine the length of that string. Lastly, we used the CHAR function to return a character specified by a number in cell B5.

• So far, here is the output against cell B5.

• Follow the same procedure and you will see the final set of split text.

How to Split Numbers from Text in Excel

The following image shows a worksheet with some numbers and texts in a single column. Our goal is to extract the number part from each cell and put these numbers into a separate column using the Flash Fill tool.

• In the beginning, insert the number of cell B5 inside cell C5.

• Then, click on the immediate next cell.
• After that, go to the Data tab and select Flash Fill.

• That’s it, you will get all the numbers at once.

Conclusion

So far, we have discussed the process of splitting text in excel with 8 practical applications. Try them and let us know your feedback. Keep an eye on ExcelDemy for more excel blogs.