How would you convert an Excel column of strings or numbers to a string as a comma separated value? Suppose you have a list of friends names like Bob, Sally, Amir and so on in an Excel column that you wanted to transform into a string like this: ‘Bob’, ‘Sally, ‘Amir’. Suppose you have a column of numbers in Excel (24, 17, 33) that you wanted to transpose into a string like this: 24, 17, 33. How would you do that? You could use the transpose function and go from there, or you could use VBA. This post uses VBA (Visual Basic for Applications).

Why would you want or need to do that? If you are working in Python, and you need to provide a list of names that you have in an Excel column, you could use the following VBA functions to accomplish this quickly. You’ll need a macro-enabled Excel file to add the code and save it. Why would you need a list in Python. You might be manually creating a DataFrame in pandas.

Below is a screenshot of an Excel macro-enabled worksheet. It has two columns of data and uses two user-defined functions to create the two strings. Column E uses the Excel formula FORMULATEXT to display what’s in the cells in column D.

VBA Code

Here below is the VBA code.

Option Explicit
Function csvRangeText(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
    csvRangeText = Left(csvRangeOutput, Len(csvRangeOutput) - 1)
End Function

Function csvRangeNumbers(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
    csvRangeNumbers = Left(csvRangeOutput, Len(csvRangeOutput) - 1)
End Function

In Python you would simply need to remove the final comma and add a bracket at the beginning and at the end, like so. You might simply copy into your favourite text editor or right into Jupyter Notebook.

['Bob', 'Sally', 'Susan', 'Rohan']

For this blog post I created an Excel file called ColumnToCSVList.xlsm.

To learn how to manually create a DataFrame in Python using the pandas library, have a look at the post Manually Create a pandas DataFrame.