CODEWITHSUNDEEP
excelvba
user793468
user793468

Reputation: 4966

Separate a comma delimited string located in a cell to a column

I have a comma delimited string (aad,adaa,,dadae,,,eresa,,baaa) in cell A1 of Sheet1 which I want to split and insert values in a column such as:

aad
adaa

dadae


eresa

baaa

Empty values will skip a cell and insert the string in next cell.

I am able to separate the string to a row but how could I do it to a column?

Here is my line to split the string to a row:

Sheets("Sheet1").Range("A1").TextToColumns

Upvotes: 1

Views: 6780

Answers (1)

GSerg
GSerg

Reputation: 78175

Call WorksheetFunction.Transpose() after splitting to columns.

No need to use TextToColumns either, Split will suffice:

Range("A1:A9").value = WorksheetFunction.Transpose(Split("aad,adaa,,dadae,,,eresa,,baaa", ","))

Upvotes: 2

Related Questions