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

Absolute Relative Reference – Dollar in Formula

Frédéric LE GUEN

Draw your borders with the mouse

Frédéric LE GUEN

Quick Analysis

Frédéric LE GUEN

8 comments

samrrr 04/07/2021 at 18:27

usefull

Reply
Johnny Santos 30/04/2021 at 00:40

You've saved my time! thanks a lot

Reply
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
Katja 16/06/2021 at 14:19

Thank you Paul , that is very good advice that helped me

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