Change default delimiters in excel

Question

I am using Excel for Mac 2016 on macOS Sierra software. Although I have been successfully copying and pasting CSV files into excel for some time now, recently, they have begun to behave in an odd way. When I paste the data, the content of each row seems to split over many columns. Where as before one cell would have been able to contain many words, it seems now as though each cell is only able to contain one word, so it splits the content of what would normally be in one cell, over many cells, making some rows of data spread out over up to 100 columns!

I have tried Data tab>> From text>> which takes me through a Text Wizard. There I choose Delimited>> Choose Delimiters: Untick the 'Space' box ('Tab' box is still ticked)>> Column data as 'General'>> Finish. Following this process appears to import the data into its correct columns. It works. BUT, a lot of work to get there!

Question: Is there any way to change the default settings of Delimiters, so that the 'Space' delimiter does not automatically divide the data?ex

Answer 1

1. Select a non-empty cell.
2. Do Data -> Text to Columns.
3. Make sure to choose Delimited.
4. Click Next >
5. Enable the Tab delimiter, disable all the others.
6. Clear Treat consecutive delimiters as one.
7. Click Cancel.
8. Now try pasting your data again