Creating a LISTSERV Subscription List from an Excel Spreadsheet

Overview:

If you have a list of email addresses as part of an Excel spreadsheet, you can format the data and export it into a text file, which you can then import as a subscriber list in a bulk operation. The following instructions will help guide you in creating a LISTSERV-friendly format.

How to format an Excel spreadsheet with subscribers:

The best format for a LISTSERV subscription list is "email address" followed by "firstname lastname." It's simple to set up the first column of email addresses. These instructions will emphasize how to use the Concatenate function and format the firstname/lastname combination that you would typically have in a contacts-style database.

  1. Remove any column headers from the top. Your final list should contain only data.

  2. In a blank column to the right of the email addresses (add a column there if you have to), create a column with combined “Firstname Lastname.” You can do this with the Concatenate function: =CONCATENATE(B1,” “,A1) or with a formula: =B1&” “&A1

    • Note: Be sure to include a blank space between the quotes so that the first and last names are separated by a space.

  3. Extend your formula to the end of the column by clicking the fill handle of that name cell and either dragging it down to the bottom of the column or by double-clicking the fill handle. Since the full names in this column were created with a formula, it will be necessary to copy them into a new column as just “values” (see step #7).

  4. Now select your two columns (the email address column and the names column) so that all of that data is highlighted.

  5. Right-click the highlighted text and click Copy.

  6. Create a new blank Excel file. By copying the formatted data to a new file, you can create a text file in the special format that you need, while still maintaining the structure of your original database for continued use.

  7. In the new blank Excel file, click Edit and then Paste Special.

  8. In the Paste Special dialog, click the radio button for “Values” and then click the “OK” button. You may want to expand the columns so that you can see all of the data.

  9. Save the data in the new Excel file by clicking File and then Save As. Browse to the location where you want to save the new file. 

    1. Provide a descriptive file name, and (this is important!) select the Save as type to be Text (Tab Delimited). 

    2. After you click the Save button, you’ll receive a warning that some features in your workbook might be lost if you save it as Text (Tab delimited). Click Yes.

  10. You can now exit out of the Excel program. When you exit, you'll be asked if you want to save your changes to the Excel file you've just created. Click on Don't Save. Since you created the text file from a new workbook, you don't need to keep that version.

  11. In Listserv, you can now go to List Management and then Subscriber Management and select the Bulk Operations tab. From there, you can browse for the location of your text file and import the data into your list.

 

Details

Article ID: 16312
Created
Thu 9/8/16 10:43 AM
Modified
Mon 1/7/19 10:48 AM