[RESOLVED] Excell: converting comma-separated array

Hello,

A little "help me please" with Excell and VB.

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?

Thank you in advance!

arseniy:

Look up the following in the Visual Basic HelpHeap:
(Menu Bar: Tools > Macro > Visual Basic Editor > Help: "Split Function")

Split Function

Description
Returns a zero-based, one-dimensional array containing a specified number of substrings.

Syntax
Split(expression[, delimiter[, limit[, compare]]])

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.

Hello Webtest,

>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 hope so. To me it's like a new dimension.

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.

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
            rngSource.Offset(1 + lItemNum, 0).Value = saResults(lItemNum)
        Next lItemNum
        
        'Move to the next source cell
        Set rngSource = rngSource.Offset(0, 1)
    Loop
 
    Set rngSource = Nothing
End Sub

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

---

Quote:

---

Originally Posted by Webtest

Either Declan is a lot faster than me, or else he has nothing better to do!

---

A little bit of both! ;)

I think either of our samples should be enough to get him started....

Declan ...

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!

arseniy ...

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.

Hello RobDog888,

Quote:

---

Originally Posted by RobDog888

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 : ....

Hello again DKenny, Webtest,

Thank you for your examples!

A short question: to get the macros working for all cells in a column F, should it be a:

VB Code:

'Start in cell F1
    Set rngSource = ThisWorkbook.Worksheets(1).Range("F1")
    
    'Loop through all used cells in column F
    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
            rngSource.Offset(1 + lItemNum, 0).Value = saResults(lItemNum)
        Next lItemNum
        
        'Move to the next source cell
        Set rngSource = rngSource.Offset(1, 0)
    Loop

?

Like, Offset specifies the vector, while Range characterizes the start and end points?

I'll test it with my worksheet tomorrow, I suppose it should solve my hassle!

Cheers!

Arseniy ...

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!

Quote:

---

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.

Quote:

---

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.

Hello again DKenny, Webtest,

Thanks again for your support.

To get the situation clear, I attached an example of what I have and what I need to have.

This is how far I got:

I understood (but not much) the VBScript you advised and this is what I'm now fronting:

1) I have to insert N blank rows after currently analyzed cell, where N = last element in array of separated sources

2) I have to delete source row with comma-separated values after I have my parsing done

Overall syntax and concepts misunderstanding makes me to irk for your help again, however, this is what I tried:

VB Code:

Option Explicit
 
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 rowsnumber As Long
Dim lItemNum As Long
 
    'Start in cell C3
    Set rngSource = ThisWorkbook.Worksheets(1).Range("C1")
    
    'Loop through all used cells in Column C
    Do While rngSource.Value <> ""
        
        saResults = Split(rngSource.Value, sSeparator)
        
        'HERE I tried to set rowsnumber = UBound(saResults) and get the number
        'of rows to be insterted
        'HERE I tried to use ThisWorkbook.Worksheets(1).addItem rowsbunmber
        'to insert blank rows of the interest
                      
        '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
            rngSource.Offset(1 + lItemNum, 0).Value = saResults(lItemNum)
        Next lItemNum
 
          
        'Move to the next source cell in column
        Set rngSource = rngSource.Offset(1 + lItemNum, 0)
 
        'HOW To delete entire row where we've just have had the source?
        'Like, ThisWorkbook.Worksheets(1).RemoveItem   ??
        'But the addressing will be hurt, isn't it?
 
        
    Loop
 
    Set rngSource = Nothing
End Sub

Thank you again for support,

I haven't even realized yesterday that I would go so far.

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!

Hey,

Thank you very much for your help guys!

Keywords:

cell, replace, Visual, Basic, Excell, delete, row, content, array, comma, separated, covert, automate

Here is what I made in the end:

VB Code:

Option Explicit
 
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 numberRows As Integer
Dim lItemNum As Long
 
    'Start in cell C1
    Set rngSource = ThisWorkbook.Worksheets(1).Range("C1")
    
    'Loop through all used cells in Column C
    Do While rngSource.Value <> ""
        
        saResults = Split(rngSource.Value, sSeparator)
        
        numberRows = UBound(saResults)
        
        rngSource.Offset(1).EntireRow.Resize(numberRows).Insert
        rngSource.EntireRow.Copy rngSource.EntireRow.Offset(1).Resize(numberRows).EntireRow
        
        On Error Resume Next
                            
        '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
            rngSource.Offset(lItemNum, 0).Value = saResults(lItemNum)
        Next lItemNum
           
 
           
        'Move to the next source cell in column
        Set rngSource = rngSource.Offset(lItemNum, 0)
        
    Loop
 
    Set rngSource = Nothing
End Sub

Attached is the example of how it works.

Cheers!

arseniy
Does this mean that it is now working as you need or is there another question?

If it is working, can you mark this thread as resolved, (under the "Thread Tools" at the top of the page)

Yep!

Thanks!