How to Split a String by Character in Excel (4 Useful Methods)

When you are analyzing usernames, cleaning up mixed data entries, or preparing text for further processing, you need to break down a string character by character. You can split a string character by character by using the built-in formulas like MID and TEXTSPLIT, to features like Power Query, Flash Fill, and even VBA. In this article, we’ll explore all the methods step by step to split a string by character in Excel.

Key Takeaways

To split a string by character using the MID and COLUMN functions, follow these steps.

➤ Click on the cell B2, Write this formula:
=MID($A2,COLUMN()-1,1)
➤ First, drag the formula across 8 columns and then drag down. You will get all the split strings by character.

In this article, we will explore four methods to split a string by character in Excel. Combining  MID with COLUMN functions, using the TEXTSPLIT Function, using Power Query and VBA Macro for One-Click Splitting.

By using the combination of the MID and COLUMN functions, we can extract each character from a string into separate cells. The MID function is used to return a specific character from a text string, while the COLUMN function helps to select the position of each character dynamically.

In the following dataset, we have mixed characters of data to be split into characters.

➤ Click on the cell B2, Write this formula:

➤ First, drag the formula across 8 columns and then drag down. You will get all the split strings by character.

The TEXTSPLIT  is a dynamic array function that can instantly break a text string into separate characters. To split by every character, the TEXTSPLIT function uses an empty string ("") as the delimiter to split each character as an individual item. It automatically updates when the source data changes.

➤ Click on the cell B2, Write this formula:

➤ Drag the formula across 8 columns and then drag to the right.

Power Query is a powerful data transformation tool to split a string into individual characters. After loading the data into Power Query, it converts each string into a list of characters and then sets the characters into a separate sheet.

➤ Select column A and then click Data > From Table/Range.

➤ In Power Query Editor: Select the column, click Home > Split Column > By Number of Characters.

➤ In the number of character box, click “1” and choose “Repeatedly” and then click OK.

➤ Click Close & Load to return the split characters.

➤ Now you can see the split characters in a different sheet.

A VBA macro is ideal for large or repetitive datasets. A macro can split each character of a string into separate columns. You need to assign the macro to a button. After clicking that button, strings will be split by character.

➤ Press Alt + F11 to open the VBA Code Editor, and then click Insert > Module.

➤ Paste the code.

Sub SplitCharacters()
    Dim cell As Range
    Dim i As Integer
    For Each cell In Selection
        For i = 1 To Len(cell.Value)
            cell.Offset(0, i).Value = Mid(cell.Value, i, 1)
        Next i
    Next cell
End Sub

➤ Select the data. Press Alt + F8 to open the Macro window, select SplitCharacters and then click Run.

➤ After running the macro, you can see the string is split by character.

Downloadable Resources

Get our practice workbook or necessary files that we have worked with to prepare this article.

Wrapping Up

In this article, we have discussed four distinct methods step by step for splitting a string by character in Excel. Feel free to download the practice file and share your thoughts and suggestions in the comment box.