Tuesday 8 May 2018

microsoft excel - Convert a column into a comma separated list


I have the task of creating a simple Excel sheet that takes an unspecified number of rows in Column A like this:


1234
123461
123151
11321

And make them into a comma-separated list in another cell that the user can easily copy and paste into another program like so:


1234,123461,123151,11321

What is the easiest way to do this?



Answer



I actually just created a module in VBA which does all of the work. It takes my ranged list and creates a comma-delimited string which is output into the cell of my choice:


Function csvRange(myRange As Range)
Dim csvRangeOutput
Dim entry as variant
For Each entry In myRange
If Not IsEmpty(entry.Value) Then
csvRangeOutput = csvRangeOutput & entry.Value & ","
End If
Next
csvRange = Left(csvRangeOutput, Len(csvRangeOutput) - 1)
End Function

So then in my cell, I just put =csvRange(A:A) and it gives me the comma-delimited list.


No comments:

Post a Comment

Where does Skype save my contact's avatars in Linux?

I'm using Skype on Linux. Where can I find images cached by skype of my contact's avatars? Answer I wanted to get those Skype avat...