Excel tips-Changing delimitors

At work, I have to use excel on a daily basis. I use Excel for each step of link building-from creating links, ordering content, uploading content to tracking what content has been used.t

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:

Happy delimitoring!

Advertisements

3 responses to “Excel tips-Changing delimitors

  1. Thew new versions do have some nice features, although all of Office is still prone to being a pain in the backside.

    • With Excel in particular-I hate that you can’t have two windows of it open separately! I do a lot of copying and pasting from one workbook to another and it’s little things like that which cause immense pain!

  2. Or when you do charts and it will randomly decide whether it want to do what you tell it ir not!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: