Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

Edit:

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.

share|improve this question
1  
try Export or save Excel files with other delimiters –  user304064 Mar 25 at 19:18
    
How can I input a non-printable character? –  kc2001 Mar 25 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 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 at 12:33
    
Ascii Decimal 31 appear as a triangle pointing down for me: ▼ –  sunk818 Oct 15 at 20:18

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
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 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 at 14:13
    
Wow. That actually worked! +1 for the Notepad explanation. –  kc2001 Mar 26 at 17:13

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:

=CONCATENATE(Sheet1!A1,CHAR(31),Sheet1!B1,CHAR(31),Sheet1!C1)

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:

=CONCATENATE(Sheet1!A1,CHAR(31),Sheet1!B1,CHAR(31),Sheet1!C1,CHAR(31),Sheet1!D1)

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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