Excel displaying data strangely

I administered a questionnaire using limesurvey and downloaded the responses in csv format to analyse in R. For some reason, the column names are displaying very weirdly in excel. I have tried Data --> Text to columns, separate by comma but as in the picture it only changes the first few into column names and the rest are displaying strangely down column 1. The actual responses only start way down in row 120.

responses only start at row 120

Does anyone have any idea how I can fix this? As when I tried reading the sheets into R it still displays strangely and I'm concerned about the analysis.

Here's the notepad display: Notepad display

  • What does the file look like in notepad/notepad++? My guess is that some characters like \r\n aren't being read properly by excel. – mcalex Feb 15 '17 at 7:48
  • @mcalex I'll attach an image of the notepad display. I'm pretty new to this kind of thing so stumped! – KirstyK Feb 15 '17 at 8:40
  • It looks like Excel is interpreting something as a hard return followed by that foreign 'A' character. I would try to manipulate a copy of the csv file in a text editor to try to locate and translate (or eliminate) whatever it is that excel doesn't like. – mcalex Feb 15 '17 at 8:49

It's one (in my opinion) very stupid thing about Microsoft Excel: By default it only accepts the list separator defined in Windows' Region settings, which is most likely a semicolon on your system. As such, Excel won't interpret the commas in your CSV file correctly.

Excel used to have a setting for this, but I never got it working properly. As such I'd suggest you modify the regional setting instead:

  • Open your Start menu, type Region and you should only get one Control Panel option. Open it.
  • On the bottom right, open the Advanced Options.
  • In the new window you'll find one setting for a list separator.
  • Change the list separator to , rather than ; or whatever you're using right now.
  • Confirm/close all the windows and try opening your CSV file again.

I don't have the English language pack installed right now, but this screenshot should give you an idea on where the option is:

Windows Regional Settings

This shouldn't have any impact on other programs, but if something doesn't work, just undo the change once you're done.

Note that if you've got decimal numbers in your CSV and they're read incorrectly, you'd also have to temporarily adjust the decimal and thousands group separator in this window.

|improve this answer|||||
  • thank you for that advice. I have already tried that and unfortunately it didn't change anything. The weird thing is that the files display normally one the two other pc's I have opened them on! so I'm convinced it must be some fault in my settings. – KirstyK Feb 15 '17 at 9:24

I eventually managed to figure out how to fix the problem! It turns out that in the options in excel, the decimal separator was set to a comma (,) instead of a point(.). By changing that, the format changed to look correct again.

I went to File --> Options --> Advanced. Then changed User System Separators decimal separator. See attached screenshot. Change decimal to point

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.