How to split text in Excel: 6 Easy Ways

Excel is highly useful when working with formulas, functions, and complex calculations. But editing content, especially text, can be a daunting task. Excel is not optimized for editing text, unlike Microsoft Word, Google Docs, or other text editing programs.

For this reason, if you want to present your data cleaner, you have to play around with its features. If you have text in the same cell and want to split it, you can still do it on Excel.

In this article, I will guide you on how to split text in Excel.

Using Convert Text to Columns Wizard

Excel already has an inbuilt feature that allows users to split text in one cell into multiple columns.

1. Select the cell you wish to split (If you have several cells you wish to split, you can highlight all the cells using the Mouse)

2. Click on the Data Tab, then Text To Columns

3. While under the text to column wizard, choose delimited and then click on next

4. After clicking next, choose the delimiter you want to use. For example, it can be a space, tab, or comma. Once you choose, preview the data to make sure it is the output you are looking for.

5. Click on next

6. Choose the destination cells that will hold the split text

7. Click on finish, and the text will be split into new columns automatically

Using Right Function

The right function, whose syntax is =Right (Text, [Number]), returns characters on a given text’s right side. Note that this formula works on Excel versions 2016 to the latest Microsoft 365.

This function can be helpful whether you are splitting text with delimiters or not. We also need to use it alongside other functions like MID, SEARCH, FIND, and LEN to locate the text we wish to split.

Assuming you have the following serial numbers but want to split them from product names:

1. Type in the following formula in the destination where you wish to display Split Text

=RIGHT(C5,LEN(C5)-FIND(“-“,C5))

“-“: Tells Excel to split text immediately after the symbol in double-quotes. You can replace the dash with the symbol applicable to your data.

2. Press Enter, and the text will be split from the serial number

3. Apply the same formula to the rest of the cells

Using Left Function

The Left is similar to the right function. However, there is one key difference. The right function will display the results of text on the right, while the Left will display the results on the Left.

Use LEFT and Find Functions to locate the delimiter, then split the text

Using Kutools

Kutools is a Microsoft Office addins by Extendoffice. It adds new functionalities and enhances the existing ones.

1. Download and install Kutools from the official Extend office website

2. Highlight the cells you want to split and click on the Kutools Tab, then Merge & Split

3. Now click on the option to split names

If you want to split cells, you can click on the split

4. Tick Split first name, Second name, and Third name. If you want to split two names only or one, tick the checkboxes accordingly

5. Click on Okay

6. Select the range to paste the results and click on okay

Kutools will split the names and paste them into individual cells

Using VBA code

You can easily split text in Excel using VBA code. In this example, I will show you how to split text with a delimiter using VBA code. Follow these steps

1. Open the workbook containing the text that you wish to split

2. Click on the developer tab, then on Visual Basic. (If you don’t see the developers tab on your Excel, then you need to enable it. Right-click on any Tab, then select Customize quick access bar. Search for the Developer tab and click on Add ). Alternatively, you can use the shortcut Alt+F11 on Windows or opt+F11 on Mac

3. Click on Insert, then select module

4. Copy and paste the following VBA code into the editor

Sub SplitText()
    Dim MyArray() As String, Count As Long, i As Variant, cellValue As String
    Dim uniqueValues As Object
    Set uniqueValues = CreateObject("Scripting.Dictionary")
    
    For n = 2 To 12
        MyArray = Split(Cells(n, 2), ",")
        Count = 3
        For Each i In MyArray
            cellValue = Trim(i)
            If Not uniqueValues.exists(cellValue) Then
                Cells(n, Count) = cellValue
                uniqueValues(cellValue) = True
                Count = Count + 1
            End If
        Next i
        uniqueValues.RemoveAll
    Next n
End Sub

5. Head over the back to the Excel sheet, Highlight all the text you wish to split, and click on the developer tab, then Macros

6. Run the Macro labeled as SplitText()

7. All the text will be split into new cells

Using Flash Fill

Flash fill is one of Excel’s most creative features. It allows users to fill bulk cells based on a few examples provided manually. It saves time and works efficiently. For this reason, you can utilize Flash fill to split bulk text in Excel

1. Write the first name to guide the Flash fill

2. Select the second name and click on the Fill drop-down, then Flash Fill

3. Once you press on the flash fill option, all the cells beneath will be automatically filled

4. Repeat the same procedure with the remaining columns

Final Thoughts

You can split text in Excel in many ways. Choose the method depending on the text format and delimiters. The easiest way to split text is by taking advantage of the Split Text Excel Wizard. Use the practice sheet provided in this tutorial to test your skills.