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

Get FREE Advanced Excel Exercises with Solutions!

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

VBA Code to Split String by Number of Characters in Excel


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

VBA Code to Split String by Number of Characters in Excel

⧭ Output:

Run the code. First, it’ll ask you to enter the string in an InputBox.

I’ve entered a random string here, abcdefghijklmnopqrstuv.

Entering Input to Split String by Number of Characters in Excel VBA

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.

Output to Split String by Number of Characters in Excel VBA

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.

Data Set to Split String by Number of Characters in Excel VBA

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.

Output 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

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

VBA Code to Split String by Number of Characters in Excel VBA

⧭ 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.


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo