Change Separators from Commas to Decimals or Decimals to Commas in Microsoft Excel

by Avantix Learning Team | Updated July 29, 2020

Applies to: Microsoft® Excel® 2013, 2016, 2019 and 365 (Windows)

Depending on your country or region, Excel may display decimal points or dots instead of commas for larger numbers. The decimal point (.) or comma (,) is used as the group separator in different regions in the world. You can change commas to decimal points or dots or vice versa in your Excel workbook temporarily or permanently. The default display of commas or decimal points is based on your global system settings (Regional Settings in the Control Panel) and Excel Options.

If you want to use a decimal point instead of a comma as a group or thousands separator, which is different from your Regional Settings, this can lead to issues with your Excel calculations.

Excel recognizes numbers without group or thousands separators so these separators are actually a format.

Your Regional Settings in your Control Panel on your device are used by default by Excel to determine if decimals should be a period or dot (used in the US and in English Canada for example) or if decimals are a comma (used in many European countries and French Canada).

Recommended article: 10 Great Excel Pivot Table Shortcuts

Changing commas to decimals by changing global system settings

It’s important to enter data in Excel in accordance with your global system settings in the Control Panel. For example, if you enter the number 1045.35 and your system is set with US as the region, the period is used as the decimal separator. If you enter the number 1045,35 in France, the comma is used as the decimal separator based on system settings with France as the region.

Below is the Control Panel in Windows 10:

Control |Pane; in Windows 10 with Regional Settings and other icons.

To change the Regional Settings in the Control Panel in Windows 10:

  1. In the Start a search box on the bottom left of the screen, type Control Panel. The Control Panel appears.
  2. Click Clock and Region. This may appear as Languages and Regional Standards.
  3. Below Region, click Change Date, Time, or Number Formats. A dialog box appears. If you change the region, Excel (and other programs) will use settings based on the selected region so US would use periods as decimals and commas as thousands separators.
  4. If you don’t want to change the region, click Additional settings. A Customize Format dialog box appears.
  5. Click the Numbers tab.
  6. Select the desired options in Decimal symbol and Digit grouping symbol. You can also change other options in this dialog box (such as the format of negatives).
  7. Click OK twice.

Below is the Customize Format dialog box:

Custom Format dialog box to change separators in Windows.

Keep in mind that this will change defaults on your device for all Excel workbooks (as well as other programs like Microsoft Access or Microsoft Project). If another user opens the workbook on a different device, the separators will display based on their system settings.

Changing commas to decimals and vice versa by changing Excel Options

You can also change Excel Options so that commas display as the group or thousands separator instead of decimals and vice versa. This option will affect any workbook you open after you have made this change.

To change Excel Options so that commas display as the thousands separator and decimals appear as the decimal separator (assuming your global settings in Regional Settings are set in the opposite way):

  1. Click the File tab in the Ribbon.
  2. Click Options.
  3. In the categories on the left, click Advanced.
  4. Uncheck Use system separators in the Editing area.
  5. In the Decimal separator box, enter the desired character such as a decimal or period (.).
  6. In the Thousands separator box, enter the desired character such as a comma (,).
  7. Click OK.

Below is the Options dialog box where Use system separators has been turned off and a decimal and comma have been entered as separators:

Excel Options dialog ox with option to use System settings unchecked.

You may want to change the Options back to Use system separators when you have finished your task.

Creating a formula to change separators and format using current system separators

Another alternative is to write a formula in another cell or column to convert separators. This works well if the data is text with separators that are not used in the current system settings. If you write a formula to convert text to numbers, you can keep the original values as well. In Excel 2013 or later, you can use the NUMBERVALUE function to convert the text to numbers.

The NUMBERVALUE function has the following arguments:

Text – The text to convert to a number.

Decimal_separator – The character used in the original cell to separate the integer and fractional part of the result.

Group_separator – The character used in the original cell to separate groupings of numbers, such as thousands from hundreds and millions from thousands.

The function is entered as follows:

=NUMBERVALUE(Text,Decimal_separator,Group_separator)

For example, if you have 2.500.300,00 in A1 and your device is set to US in the Regional Settings, you could enter the following formula in B1:

=NUMBERVALUE(A1,”,”,”.”)

The value that is returned will normally appear as a number without formatting. You can then apply appropriate formatting by pressing Ctrl + 1 and using Format Cells to apply formatting with system separators.

Changing separators using Flash Fill

Flash Fill is a utility that is available in Excel 2013 and later versions and can be used to clean up and / or reformat data. It is not a formula, so if the original data changes, you’ll need to rerun Flash Fill.

In the example below, the numbers entered in column A are formatted using system settings (US in this case). In column B, we have entered sample data in B2 and B3 with different separators (periods for the group separator and commas for the decimal separator). It’s usually best to enter a few examples to show Excel the pattern. You’ll need data with a pattern to the left in order to use Flash Fill. Once you have entered a few examples of the pattern in cells to the right of the original data, in the cell below the samples (in this case in cell B4) press Ctrl + E. Flash Fill will fill in the remaining cells until it reaches the end of the data set.

The following example was created using Flash Fill in column B (the data that is filled in column B is text):

Example worksheet to add comma and decimal separators using Flash Bill.

For more information, check out How to Clean or Extra Data Using Excel’s Amazing Flash Fill.

Changing commas to decimals in an Excel worksheet for a selected range using Replace

You can use the Replace command to replace commas with decimals or vice versa In a range of cells on a worksheet. It’s important to understand that this will change the range of cells to text so you won’t be able to use certain functions like SUM. You may want to replace on a copy of the data.

To change commas to decimals using Replace:

  1. Select the range of cells in which you want to replace commas with decimals. You may select a range of cells, a column or columns or the entire worksheet.
  2. Press Ctrl + 1 or right-click and select Format Cells. A dialog box appears.
  3. Click the Number tab.
  4. In the categories on the left, click Text and then click OK. Notably, the data will be text not numbers.
  5. Click the Home tab in the Ribbon and in the Editing group, click Find & Select. A drop-down menu appears.
  6. Click Replace. Alternatively, press Ctrl + H. A dialog box appears.
  7. In the Find and Replace dialog box, click the Replace tab.
  8. In the Find what box, enter a comma (the character that you want to find).
  9. In the Replace with box, enter a decimal or period (the character that you want to replace).
  10. Click Replace All if you are sure that you want to replace all symbols or characters in the selected range. Click Replace if you want to find and replace one by one in the selected range. Click Find All if you want to find all of the characters to be replaced first and then decide if you want to replace all. Click Find Next if you want to find characters one by one and decide if you want to replace each character by clicking Replace. After replacing all characters, Excel displays a dialog box with the number of replacements.

Below is the Find and Replace dialog box with a comma (,) entered in the Find what box and a decimal (.) in the Replace with box:

Find and Replace dialog box in Excel to find a comma and replace with a decimal.

In the previous example, only commas were changed to decimals but you may have data that has both commas and decimals (like 1,475.55). In this case, you would need to Replace multiple times.

To change commas to decimals and decimals to commas using Replace:

  1. Select the range of cells in which you want to replace commas with decimals and decimals with commas. You may select a range of cells, a column or columns or the entire worksheet.
  2. Press Ctrl + 1 or right-click and select Format Cells. A dialog box appears.
  3. Click the Number tab.
  4. In the categories on the left, click Text and then click OK. Notably, the data will be text not numbers.
  5. Click the Home tab in the Ribbon and in the Editing group, click Find & Select. A drop-down menu appears.
  6. Click Replace. Alternatively, press Ctrl + H. A dialog box appears.
  7. In the Find and Replace dialog box, click the Replace tab.
  8. In the Find what box, enter a decimal.
  9. In the Replace with box, enter an asterisk (*).
  10. Click Replace All if you are sure that you want to replace all symbols or characters in the selected range. Click Replace if you want to find and replace one by one in the selected range. Click Find All if you want to find all of the characters to be replaced first and then decide if you want to replace all. Click Find Next if you want to find characters one by one and decide if you want to replace each character by clicking Replace. After replacing all characters, Excel displays a dialog box with the number of replacements.
  11. In the Find what box, enter a comma.
  12. In the Replace with box, enter a decimal or period.
  13. Click Replace All if you are sure that you want to replace all symbols or characters in the selected range. Click Replace if you want to find and replace one by one in the selected range. Click Find All if you want to find all of the characters to be replaced first and then decide if you want to replace all. Click Find Next if you want to find characters one by one and decide if you want to replace each character by clicking Replace. After replacing all characters, Excel displays a dialog box with the number of replacements.
  14. In the Find what box, enter an asterisk (*).
  15. In the Replace with box, enter a comma.
  16. Click Replace All if you are sure that you want to replace all symbols or characters in the selected range. Click Replace if you want to find and replace one by one in the selected range. Click Find All if you want to find all of the characters to be replaced first and then decide if you want to replace all. Click Find Next if you want to find characters one by one and decide if you want to replace each character by clicking Replace. After replacing all characters, Excel displays a dialog box with the number of replacements.

Again, the result will be text, not numbers.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, join our email list.

Related

How to Convert Text to Numbers in Excel

How to Delete a Pivot Table in Microsoft Excel

How to Highlight Errors, Blanks and Duplicates in Excel Worksheets

Recommended Microsoft Excel training

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to Visual Basic for Applications (VBA)

VIEW MORE COURSES >

Our instructor-led classroom training courses are delivered at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada (some courses may also be delivered at an alternate downtown Toronto location). Contact us if you’d like to arrange custom training at your office on a date that’s convenient for you.

To request this page in an alternate format, contact us.

Copyright 2020 Avantix® Learning 

You may also like

How to Move a Pivot Table in Excel

How to Move a Pivot Table in Excel

Moving a pivot table is not as simple as moving other objects in an Excel worksheet or workbook. You will typically need to use the Move PivotTable command in the Ribbon to move a pivot table to a different area on a worksheet or to a different sheet in the same workbook.

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca

Summary
How to Change Commas to Decimal Points and Vice Versa in Excel (5 Ways)
Article Name
How to Change Commas to Decimal Points and Vice Versa in Excel (5 Ways)
Description
Depending on your country or region, Excel may display decimal points or dots instead of commas for larger numbers. The decimal point (.) or comma (,) is used as the group separator in different regions in the world. You can change commas to decimal points or dots or vice versa in your Excel workbook temporarily or permanently.
Author
Publisher Name
Avantix Learning

Pin It on Pinterest

Share This