What to expect in exported files

When you export responses, contacts or an analysis to CSV or Excel, SurveyRock makes sure nothing typed into your survey can be treated as a formula when you open the file in Excel. Google Sheets and LibreOffice should behave the same way. You may notice two things: a leading apostrophe on some cells in CSV files, and text answers that stay text in Excel files. This article explains both and what to do about them.

Why a CSV cell can start with an apostrophe

Spreadsheet programs can treat a cell that begins with =, +, - or @ (or an invisible tab or return character) as a formula. Anyone can type those characters into a survey’s text box, so an answer such as =HYPERLINK("http://example.com","Click") could be treated as a formula when you open the file.

To stop that, SurveyRock adds an apostrophe (') to the start of any CSV cell that could be treated as a formula. The apostrophe keeps the cell as text. The rest of the cell is exactly what was submitted.

What was submitted	What you see in the CSV
=1+1	'=1+1
@handle	'@handle
-N/A	'-N/A

SurveyRock does not change two kinds of cell: cells that contain only a number, such as -5, +3.2 and -12.5%, and cells made of hyphens only (-, --).

SurveyRock never changes your responses. It changes only the downloaded file.

The apostrophe also protects ordinary answers. Without it, Excel turns an answer such as -N/A into a formula and shows #NAME? where the answer should be.

To get the original text back, delete the apostrophe at the start of the cell.

How Excel exports differ

SurveyRock’s Excel (.xlsx) exports store a cell that could be treated as a formula as text, so no apostrophe is needed.

Excel exports of responses also keep answers to text questions (the ones where people type their own answer) exactly as typed:

• A phone number such as +41791234567 keeps its +.
• 0.50 stays 0.50.
• The Respondent ID, Distributor, IP Address and User Agent columns are text.

Numbers stay numbers where the question asks for a number: ratings, NPS scores, and text questions set to accept a Whole number or Decimal number. You can still total and chart those. Every other question type is exported as it was before.

In Excel files, contacts exports save every value as text. A CSV has no cell types, so Excel can still turn a phone number such as +41791234567 into a number without its +. If a contacts phone number must keep its +, use the Excel export.

Where you’ll see this

This applies to exports and downloads that include text people typed, including:

• Response exports (CSV and Excel)
• The Download CSV button on a question’s report card on the Responses dashboard
• Contacts exports
• Crosstab, Key Drivers and Text Analysis exports
• The Export CSV button in an email batch’s details in Email History

Troubleshooting

The Excel steps below use the English menu names. If your Excel is in another language, look for the same commands in your language.

Some cells in my CSV start with an apostrophe. Those cells began with =, +, - or @ and did not contain only a number. SurveyRock adds the apostrophe on purpose; see the first section. Delete it if you want the original text.

A column of numbers in my Excel export is stored as text. People can type numbers into a text question, for example an age or a count. If the question does not accept only numbers, SurveyRock saves those answers as text.

To get numbers in future exports:

1. Open the question in the survey editor.
2. Turn on Validation.
3. Choose Whole number or Decimal number.

This also limits what respondents can type, so use it only if the question should accept numbers only. Files you have already downloaded don’t change.

To convert a column you already have:

1. Select the column in Excel.
2. Choose Data, then Text to Columns.
3. Click Finish.

Once a value is a number, Excel shows 0.50 as 0.5.

A phone number in my CSV lost its +. When Excel opens a CSV, it turns a cell that holds only a + and digits into a number and removes the +. Use the Excel export instead, which keeps the text as typed. Or import the CSV so that Excel keeps every column as text:

1. In Excel, choose Data, then From Text/CSV.
2. Choose your file.
3. Next to Data Type Detection, choose Do not detect data types.
4. Click Load.

Excel names the columns Column1, Column2 and so on, and your header row appears as the first row of data.

Text Analysis says my question has too many answers to export as an Excel file. The Excel export of Text Analysis can contain at most 40,000 cells, and each answer uses several of them. Export as CSV instead. The CSV export isn’t subject to this limit.