Convert comma list to array

Topic

New Reply

Hello.
In one cell I have a list of comma separated values: e.g. ‘1,2,7’. How can I push the separate values
into a VBA array? Another example might be ‘1,22,34,40’ – so the number of items and their “width” will vary.

Thanks, Andy.

Reply | Quote

Replies

Let’s say the list is in cell A1. Use the following code.

Dim arr As Variant
arr = Split(Range(“A1”), “,”)

Note: by default, the array index will be zero-based, i.e. the first element is arr(0).
The index of the last element is UBound(arr).

Reply | Quote

Let’s say the list is in cell A1. Use the following code.

Dim arr As Variant
arr = Split(Range(“A1”), “,”)

Note: by default, the array index will be zero-based, i.e. the first element is arr(0).
The index of the last element is UBound(arr).

That’s great, thanks. I was trying to look at each character in turn to find the commas, but
it was getting a bit messy. Andy.

Reply | Quote