Comma or Semicolon in an Excel formula

Comma or Semicolon in an Excel formula
Last Updated on 18/05/2023
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

13 Comments

  1. Kay
    06/01/2023 @ 01:16

    Thank you, very useful tip

    Reply

  2. Batman
    06/07/2022 @ 18:26

    Thanks man!! very clear explanation.

    Reply

  3. Nilesh
    02/03/2022 @ 11:09

    Thanks.

    Reply

  4. mukesh dudi
    22/11/2021 @ 17:36

    very ususe full

    Reply

    • Me
      29/08/2022 @ 13:14

      You're a lifesaver. I have no idea it is using comma in English language.

      Reply

  5. samrrr
    04/07/2021 @ 18:27

    usefull

    Reply

  6. Johnny Santos
    30/04/2021 @ 00:40

    You've saved my time! thanks a lot

    Reply

  7. Michael Jonker
    15/10/2020 @ 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

  8. Diogo
    24/01/2020 @ 00:17

    Worked like a charm for me, thanks.

    Reply

  9. Paul
    12/01/2020 @ 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 @ 14:19

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

      Reply

  10. Ho
    22/10/2019 @ 16:52

    it doesnt work

    Reply

    • Frédéric LE GUEN
      23/10/2019 @ 17:13

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

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Comma or Semicolon in an Excel formula

Reading time: 2 minutes
Last Updated on 18/05/2023

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

13 Comments

  1. Kay
    06/01/2023 @ 01:16

    Thank you, very useful tip

    Reply

  2. Batman
    06/07/2022 @ 18:26

    Thanks man!! very clear explanation.

    Reply

  3. Nilesh
    02/03/2022 @ 11:09

    Thanks.

    Reply

  4. mukesh dudi
    22/11/2021 @ 17:36

    very ususe full

    Reply

    • Me
      29/08/2022 @ 13:14

      You're a lifesaver. I have no idea it is using comma in English language.

      Reply

  5. samrrr
    04/07/2021 @ 18:27

    usefull

    Reply

  6. Johnny Santos
    30/04/2021 @ 00:40

    You've saved my time! thanks a lot

    Reply

  7. Michael Jonker
    15/10/2020 @ 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

  8. Diogo
    24/01/2020 @ 00:17

    Worked like a charm for me, thanks.

    Reply

  9. Paul
    12/01/2020 @ 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 @ 14:19

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

      Reply

  10. Ho
    22/10/2019 @ 16:52

    it doesnt work

    Reply

    • Frédéric LE GUEN
      23/10/2019 @ 17:13

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

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *