3

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
    "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. – Ƭᴇcʜιᴇ007 Feb 3 '17 at 15:41
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.

  • 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. – H. Pauwelyn Feb 3 '17 at 16:04

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.