I have an array of comma-separated values posted in a single cell. It looks like something1, something2, something3, something4 etc. I have many cells across worksheet with same type of content, with different total and word lengths.
I should have a new row for each value from that cell and post the value to separate cell, creating a column after all.
I'm really "virgin" with VB, can I use a script to automate this process?
You can "Split" on the comma. Do you know how many values are in the CSV string in each cell? Can you determine which cells are populated with CSV strings? Do the separated values go on the same sheet or on a new sheet?
What you are trying to do is very easy. Give us a few answers to these basic questions and I'm sure you will get a very comprehensive answer.
>Do you know how many values are in the CSV string in each cell?
No, it differs. The thing is, I tried to use EXCELL's "FIND" function to count how many commas are there in the syntax, but it seems it's a prohibited operation (returns error).
>Can you determine which cells are populated with CSV strings?
I thought about counting how many spaces are there, but it also returns only the first space found.
>Do the separated values go on the same sheet or on a new sheet?
The same sheet.
>What you are trying to do is very easy.
>Give us a few answers to these basic questions and I'm sure you will
>get a very comprehensive answer.
I think that this is what you need. In this code I am assuming that the cells with the comma separated values are in Row "A". If they are not, you will need to change the starting position. I have also included a sample file that shows my code in action.
VB Code:
Sub arseniy_commas()
Const sSeparator As String = "," 'The value to be used to split the cells
Dim rngSource As Range
Dim saResults() As String
Dim lItemNum As Long
'Start in cell A1
Set rngSource = ThisWorkbook.Worksheets(1).Range("A1")
'Loop through all used cells in row A
Do While rngSource.Value <> ""
saResults = Split(rngSource.Value, sSeparator)
'Loop through each item in the array
For lItemNum = LBound(saResults) To UBound(saResults)
'Writing the value to the next cell below the source cell
Either Declan is a lot faster than me, or else he has nothing better to do!
As for me, I have to WORK for a living. This may be similar to Declan's code:
Code:
Option Explicit
Sub Macro1()
'Split out Comma Separated Values from SELECTION into a new list
Dim aRow As Long
Dim aCol As Long
Dim str_SplitArray() As String
' Set the initial cell location for split data list (My TEST string was in B2)
aRow = 3
aCol = 1
' Loop through the data in the selected cell
Do
' Split the long string into TWO pieces if possible
str_SplitArray = Split(Selection.Value, ",", 2)
'Write the split out value to the "New" area eliminating lead/trail whitespaces
Cells(aRow, aCol).Value = Trim(str_SplitArray(0))
'Check if there are ONE or TWO pieces to process
If UBound(str_SplitArray) > 0 Then
'TWO pieces ...
'Write the remainder back on top of the original string
Selection.Value = str_SplitArray(1)
Else 'Only ONE piece is left
'Clear out the source
Selection.Value = ""
'End the loop now
Exit Do
End If
'Advance the Row or Column for the New Data
aRow = aRow + 1
Loop
End Sub
Last edited by Webtest; Mar 27th, 2006 at 01:17 PM.
Reason: Redefined Dim for str_SplitArray() to "String" from "Variant"
I couldn't get the code to work with "Option Explicit" until I dimmed the array as "Variant". Does your code work under Option Explicit? Sometime I'll look more deeply into the data types in this operation.
Edit: I got it working. I don't know what I was doing wrong ... just in a hurry ... I've got a meeting coming up shortly so it's back to the grindstone!
Last edited by Webtest; Mar 27th, 2006 at 01:19 PM.
Take a good look at RobDog's post. If you can import the data properly, you don't need any code at all.
Also, my code barfs with a "Subscript out of range" error if your input string is terminated with a "," comma (there is no text after it). However, in this case the "UBound" function for the array Split returns a value of "-1". That will be an easy test.
How are you importing the values? You can change the delimiter so when imported it will use the comma and place each in separate cells.
No, this isn't a .csv import, this is a buggy-secretary-girl-worksheet-after-several-years.
The thing is, that it has a wrap-text cell with tons of comma-separated entries of values in each row.
This is a date->visits worksheet. She entered it in the way: cell 1 : 05/11/2005 cell 2 : 123456, 12345, 123, 12354, etc., next row: cell 1 : 05/12/2005 cell 2 : ....
This sets the Starting Point (Cell) for the scan of the source data:
Set rngSource = ThisWorkbook.Worksheets(1).Range("F1")
This Stops the looping (walking down the column) when you encounter the first empty cell:
Do While rngSource.Value <> ""
Declan's code walks ACROSS the sheet in Row 1 finding new source cells to split ...
Set rngSource = rngSource.Offset(0, 1)
He puts the split out data into the column directly below each source data cell.
If your Source data starts in Cell F1 as indicated, the following code will not work:
rngSource.Offset(1 + lItemNum, 0)
This will start cramming split out data into F2 and walk down from there! This will overwrite your data in column F, which I'm sure you do NOT want to do! However, you don't say where you DO want the data to go!
You really need to define where the split out data is going to go, and describe that to us. You have finally stated the the source data is in Column F (and implied that it terminates at the bottom of the data with the first empty cell!). If you don't have a solid plan, there is no way you can write code!
I have many cells across worksheet with same type of content, with different total and word lengths.
This is why I wrote the code to move across the row rather than down the column.
The thing is, that it has a wrap-text cell with tons of comma-separated entries of values in each row.
This implies that the data is down a row.
If so, then you don't need any code. Just select the column and in the data menu select "Text to Columns" and select comma delimited.
Declan
Don't forget to mark your Thread as resolved.
Take a moment to rate posts that you think are helpful
Do I understand this correctly ... You might have a comma separated string in cell F1 and another one in F2. You want to change the string in F1 into a vertical list starting at F1 and going down from there with each split value in a cell in the vertical list. Then, at the bottom of the list for F1, you want to continue the list going down with the split out values that were in F2 ... extending the list down. So ... now you will have a continuous list down Column F with all of the split out values from all of the Comma Separated Strings that were in Column F to begin with.
Is this correct?
If it is correct, what I would do is to use or insert a new column, say column G, and walk down column F splitting out values into column G. Once all of the values in Column F have been split into Column G, then I would just delete the old column F, and my Column G that is now the value list would automatically become Column F.
Does this make sense, and is it what you are trying to do?
Stay with us and do not worry about your English. It is evidently not your first language, but you are doing quite well!
[RESOLVED] Excell: converting comma-separated array
Reply With Quote
Originally Posted by Webtest
