Thursday, 27 November 2014

How to change the delimiter character to pipe or colon in Microsoft excel csv file? Also, change digit grouping, other format settings.

How to change the delimiter character to pipe or colon and other format settings such as digit grouping  in Microsoft excel csv file?


Every time you save a file as .csv (comma separated) file in Microsoft Excel, the file will have comma (,) as the delimiter character. In some instance, we want to change the delimiter to ; or | or other delimiter character. For those instances, you need to use the below instructions to change the delimiter settings. Similarly in the last screen shown below you can see that you change other format settings.

Step 1: Go to region and language on your control panel.


Step 2: Press the additional settings button in the region and language window.


Step3: In the numbers tab, you will see a field called List Separator. In that field enter the character you want to see as your delimiter. In the example below, I have it as ; . Press apply and you are good to go. Also in the screen below you can see that you can change other format setting such as digit grouping etc.

After you done with these setting changes, you can go back to your Microsoft excel and try to save the files in .csv format. Now the file will be saved with ; colon as the delimiter.