I've just discovered that many of the character encodings have non-printable characters designed to separate different pieces of information, e.g., ASCII character 31 is the "unit separator". It seems that if I could use one of these characters as the separator in a "comma/character separated value" (CSV), I could avoid problems in escaping the values that could occur in text, e.g., commas.

Is it possible to make MS Excel (2007) use the "unit separator" instead of a comma? If so, how can I do that?


How can I enter the non-printable character? If I try searching for Unicode "001F" in the Character Map, I get a high-lighted blank box, and a "No Character Available" message, as shown below:

enter image description here

I'd rather not install a special program just to generate the non-printable character, but I can if I have to.

  • 1
    try Export or save Excel files with other delimiters – user304064 Mar 25 '14 at 19:18
  • How can I input a non-printable character? – kc2001 Mar 25 '14 at 19:28
  • 1
    Try coping a non-printable character from Character Map and pasting it into the List Separator box from link above – user304064 Mar 25 '14 at 19:43
  • +1 for pointer to Character Map. I've edited my post to reflect my failed use of Character Map. – kc2001 Mar 26 '14 at 12:33
  • Ascii Decimal 31 appear as a triangle pointing down for me: ▼ – Sun Oct 15 '14 at 20:18

A quick test verifies that @bobSmith1432 has a workable solution.

I used Notepad++ to generate ASCII character 31 and copy it in order to paste into the List separator: field.

In Windows 7, go to Control Panel and select Region and Language. On the Format tab click the Additional settings... button. This will bring up the Customize Format window. Here, change the comma in the List separator: field to your ASCII character 31 by pasting it from some other source.

You should probably change this back to a comma immediately after saving your spreadsheet as a CSV.

Edit: As an alternative to Notepad++, you can use regular old notepad. With number lock ON, open Notepad, hold down the Alt key and hit 031 on the number pad. Then release the Alt key. Hit CtrlA and then CtrlC to select the non-printing character and copy it. You can then paste the character into the Customize Format window.

  • Thanks, Dane! +1 for the pointer to "Customize Format". I'd prefer to avoid doing a global replacement of the list separator. Knowing me, I'd forget to revert the temporary non-printable value to a more conventional one. Also, I'd prefer a solution that didn't involve installing additional software. Of course, I'll take what I can get. – kc2001 Mar 26 '14 at 12:38
  • 1
    I've added an explanation for how to do it with vanilla Notepad. If there is a built-in way to change the CSV separator, I'd love to hear about it. This method is definitely not something I'd be happy to perform, especially with any regularity. – Dane Mar 26 '14 at 14:13
  • Wow. That actually worked! +1 for the Notepad explanation. – kc2001 Mar 26 '14 at 17:13
  • thanks. It worked for me using Alt+031 directly in Region contol panel->Advanced->List separator. You see nothing but it works. – Ivan Ferrer Villa Oct 14 '16 at 12:19

I just thought of another way to do this. Create an additional worksheet within the spreadsheet you want to export. In A1 of the new worksheet, place the following formula:


Change Sheet1 to be the name of the worksheet you want to export as CSV. Repeat the pattern for additional columns. The above formula grabs columns A, B, and C. For D as well, use the following:


Hopefully you can see the pattern there. Copy this cell down for as many rows as you want. CHAR(31) is inserting the ASCII character you want.

Then, save as CSV, and only export this new sheet. Because you only have a single column, no commas or other separators will be added.

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.