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
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 DisplayTherefore, 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 VBA: Split String by Character
How to Split String by Number of Characters in Excel VBA: 2 Suitable Examples
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.
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
