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 use Macro

Here are the instructions to use above macro in Visual basic editor.

• Open Visual Basic Editor(VBE) by clicking Alt +F11
• Go to code window by clicking F7
• Copy above specified macro or procedure
• Paste above copied code in code window
• Run macro by clicking F5 or Run command
• You can see output on the screen
• Find above specified output screenshot.

VBA Arrays other articles

You may also like the related VBA Array articles.

Back to VBA Arrays