Converting a Range of Cells into Comma-Separated Values

Harrison knows that he can save a worksheet as a CSV file. However, he only needs to convert the range C2:J51 into a CSV. He wonders if there is an easy way to do this.

There are several ways you can do this. A simple way is to open both the source workbook and a brand-new workbook. Copy the C2:J51 range and paste it into the new workbook. (You can use Paste Values to paste the information.) Then you can save the new workbook in CSV format. (Harrison said he knows how to do this.)

Another approach is to open the source workbook and a new Notepad document. In the workbook, enter the following formula into cell L2:

=TEXTJOIN(",",FALSE,C2:J2)

Copy this down to the range L3:L51, and you end up with strings of comma-separated values. Select the range L2:L51, copy it, and paste it within the Notepad document. Save it using a filename extension of CSV, and you have your desired CSV file.

There is one potential gotcha to this—if your source information includes cells that contain commas. In that case, you'll want to modify the formula so that it adds quote marks around the cell contents:

=CHAR(34)&TEXTJOIN(CHAR(34)&","&CHAR(34),FALSE,C2:J2)&CHAR(34)

If you need to do this sort of thing routinely, then you could benefit by using a macro to do the conversion and saving. Here's one that is rather robust:

Sub ExportRangeToCSV()
    Dim row As Range
    Dim cell As Range
    Dim line As String
    Dim output As String
    Dim f As Integer
    Dim s As String
    Dim sFile As Variant

    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a worksheet range first."
        Exit Sub
    End If

    For Each row In Selection.Rows
        line = ""
        For Each cell In row.Cells
            s = cell.Text   ' Change to .Value if underlying value is wanted
            If InStr(s, """") > 0 Then
                s = Replace(s, """", """""")
            End If

            If InStr(s, ",") > 0 Or InStr(s, """") > 0 Or _
              InStr(s, vbCr) > 0 Or InStr(s, vbLf) > 0 Then
                s = """" & s & """"
            End If
            line = line & s & ","
        Next cell
        If Len(line) > 0 Then
            line = Left(line, Len(line) - 1)
            output = output & line & vbCrLf
        End If
    Next row

    sFile = Application.GetSaveAsFilename( _
      InitialFileName:="export.csv", _
      FileFilter:="CSV Files (*.csv), *.csv")

    If sFile <> False Then
        f = FreeFile
        Open CStr(sFile) For Output As #f
        Print #f, output;
        Close #f
        MsgBox "CSV File Saved"
    Else
        MsgBox "File Not Saved"
    End If
End Sub

The macro assumes that you are selecting the cells you want to output in a CSV file. It correctly handles special characters within a cell, such as commas, tabs, newlines, and quote marks. It also allows the user to specify a filename and location for the file.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13982) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.