In this article, I’ll show you how you split a string by any given number of characters using VBA in Excel.
Split String by Number of Characters with Excel VBA (Quick View)
Sub Split_String_by_Number_of_Characters()
Input_String = InputBox("Enter the String: ")
Number_of_Characters = Int(InputBox("Enter the Number of Characters: "))
Dim Output() As Variant
If Len(Input_String) Mod Number_of_Characters = 0 Then
ReDim Output(Int(Len(Input_String) / Number_of_Characters) - 1)
Else
ReDim Output(Int(Len(Input_String) / Number_of_Characters))
End If
For i = LBound(Output) To UBound(Output)
Output(i) = Mid(Input_String, (i * Number_of_Characters) + 1, Number_of_Characters)
Next i
For i = LBound(Output) To UBound(Output)
If i <> UBound(Output) Then
Display = Display + Output(i) + vbNewLine
Else
Display = Display + Output(i)
End If
Next i
MsgBox Display
End Sub
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
An Overview to Split String by Number of Characters in Excel VBA
Without further delay, let’s go to our main discussion today. Let’s see how we can split a given string by a given number of characters with step-by-step analysis.
⧪ Step 1: Inserting the Inputs
First of all, we have to insert the inputs into the code. There are 2 inputs required in this code: the string and the number of characters.
You can specify them inside the code, or you can use an InputBox to let the user input them each time the code is run.
I’ve used an InputBox here.
Input_String = InputBox("Enter the String: ")
Number_of_Characters = Int(InputBox("Enter the Number of Characters: "))
⧪ Step 2: Declaring the Output Array and Specify the Number of Elements of the Array
Next, we’ll declare an array called Output to contain the split parts of the string. After declaring the array, we’ll specify the number of elements of the array.
Dim Output() As Variant
If Len(Input_String) Mod Number_of_Characters = 0 Then
ReDim Output(Int(Len(Input_String) / Number_of_Characters) - 1)
Else
ReDim Output(Int(Len(Input_String) / Number_of_Characters))
End If
⧪ Step 3: Splitting the String into the Array
This is the most important step. We’ll iterate through a for-loop to split the string by the given number of characters into the output array.
We’ll use the Mid function of VBA for this purpose.
For i = LBound(Output) To UBound(Output)
Output(i) = Mid(Input_String, (i * Number_of_Characters) + 1, Number_of_Characters)
Next i
⧪ Step 4 (Optional): Displaying the Array
Finally, we’ll iterate through another for-loop to display the split string. We’ll use the vbNewLine object of VBA to display each element of the array in a new line for the sake of visualization.
For i = LBound(Output) To UBound(Output)
If i <> UBound(Output) Then
Display = Display + Output(i) + vbNewLine
Else
Display = Display + Output(i)
End If
Next i
MsgBox Display
Therefore, the complete VBA code will be:
⧭ VBA Code:
Sub Split_String_by_Number_of_Characters()
Input_String = InputBox("Enter the String: ")
Number_of_Characters = Int(InputBox("Enter the Number of Characters: "))
Dim Output() As Variant
If Len(Input_String) Mod Number_of_Characters = 0 Then
ReDim Output(Int(Len(Input_String) / Number_of_Characters) - 1)
Else
ReDim Output(Int(Len(Input_String) / Number_of_Characters))
End If
For i = LBound(Output) To UBound(Output)
Output(i) = Mid(Input_String, (i * Number_of_Characters) + 1, Number_of_Characters)
Next i
For i = LBound(Output) To UBound(Output)
If i <> UBound(Output) Then
Display = Display + Output(i) + vbNewLine
Else
Display = Display + Output(i)
End If
Next i
MsgBox Display
End Sub
⧭ Output:
Run the code. First, it’ll ask you to enter the string in an InputBox.
I’ve entered a random string here, abcdefghijklmnopqrstuv.
Click OK. It’ll ask you to enter the number of characters in another InputBox. Here I’ve entered 4.
Click OK and you’ll get the given string split by the given number of characters in a Message Box.
Read More: Excel Formula to Split: 8 Examples
2 Suitable Examples to Split String by Number of Characters in Excel VBA
We’ve learned how to split a string by a given number of characters using Excel VBA. Now we’ll explore a few examples of this.
1. Developing a Macro to Split String by Number of Characters in Excel VBA
Here we’ve got a data set extending over the range B3:D12 that contains the names of some clients, their account numbers, and the user IDs of a bank.
Our objective is to develop a Macro to split the User IDs into a given number of characters. Let the given number of characters be 3 here (You are welcome to set it according to your need inside the code).
The split parts will be separated by spaces ( ) here (You can also change it according to your need inside the code).
We’ll split the IDs in the range E3:E12 of the worksheet (You can also change it).
The VBA code will be:
⧭ VBA Code:
Sub Split_String_by_Number_of_Characters()
Set Input_Range = Range("D3:D12")
Number_of_Characters = 3
Separator = " "
Set Output_Range = Range("E3:E12")
Dim Output() As Variant
For i = 1 To Input_Range.Rows.Count
For j = 1 To Input_Range.Columns.Count
Input_String = Input_Range.Cells(i, j)
If Len(Input_String) Mod Number_of_Characters = 0 Then
ReDim Output(Int(Len(Input_String) / Number_of_Characters) - 1)
Else
ReDim Output(Int(Len(Input_String) / Number_of_Characters))
End If
For k = LBound(Output) To UBound(Output)
Output(k) = Mid(Input_String, (k * Number_of_Characters) + 1, Number_of_Characters)
Next k
Display = ""
For k = LBound(Output) To UBound(Output)
If k <> UBound(Output) Then
Display = Display + Output(k) + Separator
Else
Display = Display + Output(k)
End If
Next k
Output_Range.Cells(i, j) = Display
Next j
Next i
End Sub
⧭ Output:
Run the code (Obviously after changing the inputs). It’ll split the User IDs by 3 characters into the range E3:E12.
Read More: Excel VBA: Split String into Rows (6 Ideal Examples)
Similar Readings
- How to Split Cells in Excel (5 Easy Tricks)
- VBA to Split String into Multiple Columns in Excel (2 Ways)
- Excel Formula to Split String by Comma (5 Examples)
2. Creating a User-Defined Function to Split String by Number of Characters in Excel VBA
Now, we’ll develop a User-Defined function to split a string by a given number of characters.
The VBA code will be:
⧭ VBA Code:
Function Split_String(Input_String, Number_of_Characters, Separator)
Dim Output() As Variant
If Len(Input_String) Mod Number_of_Characters = 0 Then
ReDim Output(Int(Len(Input_String) / Number_of_Characters) - 1)
Else
ReDim Output(Int(Len(Input_String) / Number_of_Characters))
End If
For i = LBound(Output) To UBound(Output)
Output(i) = Mid(Input_String, (i * Number_of_Characters) + 1, Number_of_Characters)
Next i
For i = LBound(Output) To UBound(Output)
If i <> UBound(Output) Then
Display = Display + Output(i) + Separator
Else
Display = Display + Output(i)
End If
Next i
Split_String = Display
End Function
⧭ Output:
Select any cell in your worksheet and enter the formula:
It’ll split the User ID from cell D3 by 3 characters.
Then you can drag the Fill Handle to repeat the same for the rest of the IDs.
Read More: Excel VBA: Split String into Cells (4 Useful Applications)
Conclusion
So, this is the way to split a string by a number of characters with VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.
Related Articles
- How to Make Two Lines in One Cell in Excel (4 Methods)
- Split a Cell into Two Rows in Excel (3 ways)
- How to Split One Cell into Two in Excel (5 Useful Methods)
- Excel Split Cell by Delimiter Formula
- How to split a single cell in half in Excel (diagonally & horizontally)
- How to Split Cells in Excel (The Ultimate Guide)