Splitting Records in Excel VBA

For data import, it is usually necessary to split concatenated records beforehand. The Split() function reverses the operation of the Join() function. It converts a string into a one-dimensional array. The individual parts of the record must be separated by a defined delimiter character to be correctly recognized.

Here is an example:

Sub SplitRecords()
    Dim i As Integer
    Dim arr() As String   
    ThisWorkbook.Worksheets("Sheet1").Activate  
    arr = Split(Cells(4, 1).Value, "#")   
    For i = 0 To 2
        Cells(5, i + 1).Value = arr(i)
    Next i
End Sub

Explanation:
A dynamic array (with variable size) is declared.

The Split() function breaks the string into parts and assigns the result to the array. The delimiter used here is the # character. The first element of the array has index 0.

If no delimiter is specified, a space is used by default for splitting.

The individual elements of the array are then written into three adjacent cells in the worksheet. Note that the first element starts at index 0.