4

I've a CSV file opened with Excel. This file use comma separators (,) but the default separator for Excel (with European configuration) is a semicolon (;).

Note: I know in America they use a comma separator but in Europe we use a semicolon separator. This we do because a comma is used for a decimal operator.

The Excel developers knows that this is an issue for European CSV files (with ;) and use also a semicolon because Excel and my computer is European configurated.

The file I'll open is now an American CSV file that uses commas as separator.

My question is how can I dynamic change the separators in Excel 2016 without changing the configuration of my computer every time?

enter image description here

If I will save the file and I've three options:

  • CSV (MS-DOS) (*.csv)
  • CSV (Macintosh) (*.csv)
  • CSV (seperated by list separator stabbing comma delimited) (*.csv)1

See image below

1 In Dutch version and image: gescheiden door lijstscheidingsteken. No idea what this means.

enter image description here

I've tried this three options but none will separate the CSV file correct.

Update 1

The answer from @Gary's Student is good but the commas are changed again to a semicolon. This is not what I want.

1
  • 1
    "CSV (geschijden door lijstscheidingsteken)" is "CSV (comma delimited)" in the English Excel, which I would expect forces it (never actually tested) to save as an actual CSV (Comma Separated) file, regardless of regional format settings. Feb 3, 2017 at 15:41

1 Answer 1

2

You don't need to change the Regional Setting at all. From the Ribbon:

Data ->Get External Data -> From Text

Then tell the Text Import Wizard which separator to use.

1
  • I've unaccepted your answer because every separator is changed again from a comma to a semicolon. This is not what I want to have. Feb 3, 2017 at 16:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .