VBA Split String into Array in Excel

VBA Split String into Array in Excel is regular task in data analysis. In this tutorial we use Dim statement, VBA Split function, array and Delimiter.

Dim statement is used to declare array and string type variable. Here not defining the array size. VBA Split function creates an array to store an array elements. We use comma delimiter to split string.

Macro Code for VBA Split String into Array in Excel

Let us see the example macro code for VBA Split String and store elements in Excel. These elements are stored in an array.

'VBA Split String into Array in Excel
Sub VBA_Split_String_into_Array()

    'Variable Declaration
    Dim aString() As String
    Dim sInputString As String, sDelimiter As String
    
    'Input Value
    sInputString = "Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday"
    
    'Delimiter Value
    sDelimiter = ","
    
    'Split string into array
    aString = Split(sInputString, sDelimiter)
    
    'Displau Output Message
    MsgBox "Array First Value : " & aString(0) & vbCrLf & "Array Second Value : " & aString(1) & vbCrLf & _
    "Array Third Value : " & aString(2) & vbCrLf & "Array Fourth Value : " & aString(3) & vbCrLf & _
    "Array Fifth Value : " & aString(4) & vbCrLf & "Array Sixth Value : " & aString(5) & vbCrLf & _
    "Array Seventh Value : " & aString(6), _
    vbInformation, "VBAF1"
        
End Sub

Here is the output screen shot of the above macro.

You can view array elements in watch window.

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions. Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge. VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog