ExcelInsider Team
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.
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.
Combining MID with COLUMN Function
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.
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.
Using TEXTSPLIT Function
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.
Steps:
➤ Click on the cell B2, Write this formula:
=TEXTSPLIT(TEXTJOIN(",",,MID(A2,SEQUENCE(LEN(A2)),1)),",")
➤ Drag the formula across 8 columns and then drag to the right.
Using Power Query
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.
Steps:
➤ 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.
VBA Macro for One-Click Splitting
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.
Steps:
➤ 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.
Frequently Asked Questions
How can I split only a part of the string?
By using the MID function, you can split only a part of the string.
How many characters are allowed to be split in Excel?
Up to 32,767 characters are allowed by Excel. So you can split characters up to this limit. Otherwise, you can face layout or performance problems.
Does the original data change when characters are separated?
When characters are split, the original data will not change unless it is deleted by hand.
Why is the TEXTSPLIT function not working in my Excel?
The TEXTSPLIT function will not work in any older version of Excel. TEXTSPLIT is only available in Excel 365 and Excel 2021.
Is it possible to remove empty columns or characters after splitting?
Yes, by using the FILTER function, you can remove empty columns or characters after splitting. You can use the “Remove Columns” or “Filter Rows” options when you are using the Power Query option. You can add conditions in the macro to skip blanks when you are applying VBA.
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.
