Using Excel on pretty much a daily basis, I’ve discovered a few things that were super useful and some of the quirks of Excel. So I thought I’d pass these onto the Excel newbies. I’m no expert (yet) but I hope these posts could be found useful by someone.
If you are saving as CSV
When saving as CSV, your separate cells in a row are basically mashed into one cell and separated by a symbol of some sort such as a comma, semi colon, colon etc –these are called delimitors. Excel’s default delimitor is a comma.
So when you reopen the CSV your cells would look something like this if you save Excel data as CSV and don’t change the delimitor:
However, when you use Excel to Save as CSV, it doesn’t give you a choice of delimitor. It just separates you data with a comma. There is a site I upload a CSV to that requires the CSV to be separated by a semi colon, or it wouldn’t work.
To get round this, I copy and paste my data into Open Office’s Calculator where you can save as CSV and determine what delimitor it uses! Check the Edit Filter Settings box and click Save:
You then want to click Keep Current Format when this pops up:
When this dialog box appears, change the delimitor as you please! Field delimitor is what you want to separate your values with, the Text delimitor allows you to change how you want the finished (mashed together cell) cell to be enclosed by (not 100% sure on this..correct me if I’m wrong using the comments!) :
Tada! Delimitors changed to a semi colon: