Excel VBA: Split String by Number of Characters (2 Easy Methods)

In this article, I’ll show you how you split a string by any given number of characters using VBA in Excel.

Table of Contents hide

Split String by Number of Characters with Excel VBA (Quick View)

Download Practice Workbook

An Overview to Split String by Number of Characters in Excel VBA

2 Suitable Examples to Split String by Number of Characters in Excel VBA

1. Developing a Macro to Split String by Number of Characters in Excel VBA

2. Creating a User-Defined Function to Split String by Number of Characters in Excel VBA

Conclusion

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.

Split String by Number of Characters.xlsm

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.

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.

Similar Readings

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:

=Split_String(D3)

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.

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.