Split comma delimited cell into columns

Question

Soo ive found a lot of similar questions but nothing that really fits what im looking to do, and im a little bit stuck.

Basically what im looking to do is have a cell (in this instance, A1), that has multiple values separated by commas (always 4 values), and then have it split into separate rows along columns.

Example

A1[10,9,8,6]

Needs to Become

a10[10], b10[9], c10[8], d10[6]

The current code im working with is :

Dim X As Variant
X = Split(Range("A1").Value, ",")              

Range("a10").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)

This outputs the data vertically down a column though, when i need it to be outputted as i have shown above

`Range("a10").Resize(1, UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)` — David Zemens, Jul 23 '14 at 02:34

David Zemens · Accepted Answer · 2014-07-23T13:21:04.980

1

You're resizing Range("A10") in to a column range.

The Resize method takes two arguments, both optional:

.Resize(_rows_, _columns_)

You have only supplied the first argument, which resizes A10 to a specified number of rows. For example, Debug.Print Range("A10").Resize(10).Address should give you: $A$10:$A$19, when what you want is $A$10:$J$10.

Do this instead:

Range("a10").Resize(1, UBound(X) - LBound(X) + 1).Value = X

Also you're removing the call to Application.Transpose, since the array is already in a row, you don't need to transpose it (previously you had to transpose it from a row, to a column).

ALSO the result of the Split function is always a 0-based array, even if you have Option Base 1. So you can simply do:

Range("a10").Resize(1, UBound(X) + 1).Value = X

Alternatively, omit the first argument but include a comma to indicate that you're only passing the second argument:

Range("a10").Resize(, UBound(X) + 1).Value = X

The other method as Pieter suggests below would be to record (and modify) a macro using the built-in Text-to-Columns functionality. I don't think this will work for your case, because you're taking values from one row and putting them in another row. The built-in text-to-columns only outputs to the same row, so a custom split like you have created is the way to go.

edited Jul 23 '14 at 13:21

answered Jul 23 '14 at 02:36

David Zemens

53,033
11
81
130

Thanks heaps David! im still having a bit of trouble though. With the changes it does output across columns, however it will only do the first value (in this case '10') across all 4 columns (a10[10], b10[10], c10[10], d10[10]), how can i fix this? thanks heaps for the reply! cheers J – user3779771 Jul 23 '14 at 13:10
Hmmm get rid of `Application.Transpose` – David Zemens Jul 23 '14 at 13:19
that did it! would there be any way to make there a gap in between the cells the values are entered into as well? ie a10,c10,e10,g10 instead of a10,b10,c10,d10? Thanks mate really appreciate the help – user3779771 Jul 23 '14 at 13:26
There's a way to do it; it's not overly complicated but it would have to involve iteration in a `For Each ... Loop`, can't be done in a single statement like above. If this answered the original question, please consider "accepting" it, and if you have trouble with the `For Each ...` loop, it would be best to ask as a separate question :) – David Zemens Jul 23 '14 at 13:31

score 0 · Answer 2 · answered Jul 23 '14 at 02:36

Select the column containing the CSV values (Column A it seems in your example) and then from the Data Ribbon Data Tools group launch the **Text to Columns wizard. Specify your column s bein delimited with comma as the delimiter and watch the wizard do it's thing.

In order to program this operation in VBA, run it manually while recording a macro and then inspect and edit the macro.

Split comma delimited cell into columns

2 Answers2