Do you ever have to take a list of values provided in Excel (or raw text) and massage them into a comma-separated list? I find myself doing this A LOT. And it usually involves taking a very round-about approach of pasting into a text program and getting creative with find/replace.
Side note: use “^p” in MS Word’s find/replace dialog as code for a carriage return. “^t” is code for a tab character. Not ideal, but sometimes MS Word is the closest tool at hand.
Anyway, I’d done that enough, and I decided to write a custom Excel VBA function called “Range2Csv” to comma-delimit a range of cells. (You can actually use any delimiter you choose; pipes, semi-colons, etc., doesn’t need to be a comma). It’s not a bullet-proof function, because bullet-proof was not my goal (i.e., it doesn’t conditionally wrap cell values in quotations, and it always removes the delimiter character if it appears in the values, etc). I already had clean data and just needed to make something work, and this function did the job for my basic use case.
Feel free to make it work for you too. Suggestions are welcome in the comments below… although I’m not sure put a lot more time into this. The code is heavily commented so you should be able to customize it yourself.
Option Explicit '********************************************** '* PURPOSE: Concatenates range contents into a '* delimited text string '* '* FUNCTION SIGNATURE: Range2Csv(Range, String) '* '* PARAMETERS: '* Range - the range of cells whose contents '* will be included in the CSV result '* String - delimiter used to separate values '* (Optional, defaults to a comma) '* '* AUTHOR: www.dullsharpness.com '* '* NOTES: This function strips occurrences of the '* delimiter from within the cell values, so '* choose a delimiter that doesn't appear '* in the cell values or that you don't mind '* removal of. For example, running Range2Csv '* with a "," delimiter on a cell that '* contains "New York, NY" will cause the '* CSV result string to contain "New York NY" '* with the comma removed. '* '* Range2Csv ignores blank cells in the Range. '* '* This function was designed for a very '* simple use case, and was only slightly '* modified for publication afterward in a '* way that makes it more versatile. That is, '* it doesn't attempt to be fail-proof or '* robust. Modify it as you see fit if you '* need something stronger. '* '********************************************** Public Function Range2Csv(inputRange As Range, Optional delimiter As String) Dim concattedList As String 'holder for the concatted CSVs Dim rangeCell As Range 'holder cell used in For-Each loop Dim rangeText As String 'holder for rangeCell's text 'default to a comma delimiter if none is provided If delimiter = "" Then delimiter = "," concattedList = "" 'start with an empty string 'Loop through each cell in the range to append valid contents For Each rangeCell In inputRange.Cells rangeText = rangeCell.Value 'capture the working value 'Only operate on non-blank cells (i.e. Length > 0) If Len(rangeText) > 0 Then 'Strip any delimiters contained w/in the value itself rangeText = WorksheetFunction.Substitute(rangeText, delimiter, "") If (Len(concattedList) > 0) Then 'prepend a delimiter to the new value if we 'already have some list items concattedList = concattedList + delimiter + rangeText Else 'else if the list is blank so far, 'just set the first value concattedList = rangeText End If End If Next rangeCell 'Set the return value Range2Csv = concattedList End Function