I have some data in an excel spreadsheet that I need to reformat. The existing format is in rows. The desired format is comma separated.
Elsewhere on superuser I found a VBA script that gets me about 75% of the way there...
Sub generatecsv() Dim i As Integer Dim s As String i = 1 Do Until Cells(i, 1).Value = "" If (s = "") Then s = Cells(i, 1).Value Else s = s & "," & Cells(i, 1).Value End If i = i + 1 Loop Cells(1, 2).Value = s End Sub
The shortcomings of this script for my intended workflow currently are:
The range of cells must always be in column 1. I need to have the script take input from a selected range anywhere in the worksheet.
The output of the script always appears at Cell(1,2). I'd like to have it copied to the clipboard.
I was hoping that something like s.Copy or s.PutInClipboard would work but am getting errors.
Data looks like this:
Topic 1 Mike Tony Dave Topic 2 Steve Joe Sally Topic 3 ...
The desired output would be:
Mike, Tony, Dave
Then on the next iteration:
Steve, Joe, Sally