Excel VBA Range to CSV (Range2Csv) Function

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.

Range2Csv In Action

Range2Csv In Action

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

3 thoughts on “Excel VBA Range to CSV (Range2Csv) Function

  1. Cailey

    This is brilliant and exactly what I was looking for. THANK YOU for making this publicly available and so easy to use — you just saved me hours of headaches over the next coming months!

    Reply

Leave a Reply

Your email address will not be published.

*