CODEWITHSUNDEEP
excelvba
Clauric
Clauric

Reputation: 1886

Separate values in cell into an array

I have multiple cells that contain values separated by commas. Some of these cells start with either an apostrophe, or a comma, while others start with numbers. I have tried to use arrNew = range("A1").value (where cell A1 contains the data to be put into the array) but this only returns a variable containing the entire string.

Is there any way to put split the data, using VBA, so that each value in the cell is correctly in the array as a separate element?

Upvotes: 0

Views: 392

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Give this a try:

Public Function parsee(s As String)
    Dim arr, temp, i As Long
    arr = Split(s, ",")
    ReDim temp(1 To 1, LBound(arr) To UBound(arr))
    For i = LBound(arr) To UBound(arr)
        temp(1, i) = arr(i)
    Next i

    parsee = temp
End Function

enter image description here

Just select the block of cells adjacent to A1 and array-enter the formula.

Because I am using Excel 365, the formula does not need to be array-entered; it just spills horizontally.

Upvotes: 3

Related Questions