Beginner

Comma or Semicolon in an Excel formula

Reading Time: 2 minutes

Why does Excel expect commas or semicolons to separate the parameters in a formula? The answer can be found in this article.

Comma or semicolon ?

If you read this article, it's because one day you noticed that the separator between the parameters in any function is the comma sign or the semicolon.

Sometimes the separator is a comma

Comma as separator in formula
Comma separator

Sometimes the separator is a semicolon

Semicolon as separator
Semicolon separator

How and where to change this option?

Don't waste your time to find this option in Excel! This option is not in Excel but in the local settings of Windows 😮😮😮

Windows settings

To switch between comma or semicolon as separator, follow the next steps

1. Open your Windows settings

2. Select the Time & Language menu

Windows Setting

3. Then select Region & language > Additional date, time & regional settings

4. Click on Region > Change location

Shortcuts

You can open the same dialog box with the command line intl.cpl in the search bar of Windows 😉👍

Change your setting

When you select a country in the dropdown list, the default date and number settings are loaded.

US settings

As you can see in the picture, the list separator is a comma. Also the date format is M/d/yyyy (month, day, year)

Now, when you write a formula, the separator between each arguments is a comma. And for the date as well, automatically your dates will be written with the US date format.

And You don't have to Restart Excel to apply the settings. The change is immediate 😎😃👍

France, Spanish settings

If you don't mind, you can change the values of the country to load semi-colon as default list separator. It's the case for France or Spain. But in that case, your dates will be displayed in French or Spanish

Related posts

Remove spaces in Excel with TRIM

Frédéric LE GUEN

Convert roman numerals with Excel

Frédéric LE GUEN

Examples of Paste Special (All Options)

Frédéric LE GUEN

5 comments

Michael Jonker 15/10/2020 at 14:57

Works great, thanks!

I initially searched all over excel. Still a pity this option cannot be easily set in excel in the context of csv files only. Sometime I need semicolons, sometimes I need commas, and it is often not up to me to decide...
In most cases it should be rather trivial to determine if a csv is comma based or semicolon based.

Reply
Diogo 24/01/2020 at 00:17

Worked like a charm for me, thanks.

Reply
Paul 12/01/2020 at 20:22

In order for this to work, if you want to change from ";" to "," in addition to the steps detailed here, make sure that you are using "." as decimal symbol. If you are using "," as the decimal symbol it won't work.
Thanks Frédéric for your website. It's really useful.

Reply
Ho 22/10/2019 at 16:52

it doesnt work

Reply
Frédéric LE GUEN 23/10/2019 at 17:13

What's wrong? There is no secret here. It should work

Reply

Leave a Comment