Change Commas to Dots and Vice Versa in Excel

The majority of English-speaking countries, including Australia, the UK, and the USA, use commas to separate groups of thousands and dots to separate decimals.

Other countries such as France, Spain, and Denmark flip this around and use dots to separate groups of thousands and commas to separate decimals.

Therefore, as you work with Excel in an international setting, sometimes you may be required to change commas to dots and vice versa to show numbers in a suitable format for your audience.

This tutorial demonstrates five methods of changing commas to dots and vice versa in Excel.

Method #1: Switch the System Separators in Excel Options Dialog Box

This technique uses the Excel Options dialog box to switch the system separators.

Suppose we have received the following dataset from our contractor. He has used commas to separate groups of thousands and dots to separate decimals.

We want to share the dataset with our partners who live in Spain, where they use dots to separate thousands and commas to separate decimals.

We have to change the commas to dots and the dots to commas.

We use the following steps:

  1. Click File to open the Backstage window.
Graphical user interface, application

Description automatically generated
  1. Choose Options on the left sidebar of the Backstage window.
A picture containing chart

Description automatically generated
  1. Click Advanced on the left sidebar of the Excel Options dialog box.
  1. In the Editing Options segment of the dialog box, deselect the Use system separators option and replace the dot in the Decimal separator text box with a comma (,) and the comma in the Thousands separator text box with a dot (.) and click OK.

The commas and dots in the example dataset are switched:

Note: To switch back to the default system separators, open the Excel Options dialog box and select the Use system separators option.

Method #2: Adjust the Windows Regional Settings

This method involves adjusting the Windows regional settings to switch the system separators in the Excel Options dialog box.

In our demonstration, we use the following example dataset: commas separating groups of thousands and dots separating decimals.

Table, Excel

Description automatically generated

Our goal is to change the commas to dots and the dots, to commas.

We use the steps below:

  1. From the Windows Search box, search and open the Contol Panel App.
  1. In the Control Panel window, select Change date, time, or number formats under the Clock and Region option.
  1. Click the Additional settings button in the Formats tab of the Region dialog box that appears.
  1. In the Numbers tab of the Customize Format dialog box, open the Decimal symbol drop-down and select the comma symbol. Then open the Digit grouping symbol drop-down and select the dot symbol.
  1. Click Apply and OK.
  2. Click Apply and OK in the Region dialog box.

When we open the Excel worksheet containing the dataset, we see that commas have been changed to dots, and the dots have been changed to commas.

Table

Description automatically generated

Please note that the changes we have made now affect all the applications on the computer. The dot has become the default system separator for thousands and the dot has become the default separator for decimals.

If we want to restore the system’s original default separators, we navigate to the Customize Format dialog box, as explained earlier in this Method #3, and click the Reset button in the Numbers tab.

Click Yes on the message box that pops up.

Click OK on the Customize Format dialog box and click Apply and OK on the Region dialog box.

The system’s default separators are restored.

Method #3: Apply the Replace Feature of Notepad

This method involves copying the Excel data and pasting it into Notepad. We then switch the commas and dots in Notepad, copy the data, and paste it back into Excel.

In our illustration, we use the following example dataset: commas separating groups of thousands and dots separating decimals.

Table, Excel

Description automatically generated

We want to change the commas to dots and the dots to commas.

We use the steps below:

  1. Select the dataset and press Ctrl + C to copy it.
  1. Search and open the Notepad App from the Windows search box.
  1. Press Ctrl + V to paste the data into the Notepad.
Text

Description automatically generated
  1. Open the Notepad Replace dialog box in any of the following ways:

Press Ctrl + H

Or

Select Replace on the Edit menu.

Graphical user interface, application

Description automatically generated
  1. In the Replace dialog box, type a dot in the Find what text box and a question mark (?) in the Replace with text box, then click the Replace All button.

Note: The question mark is a temporary placeholder that shall be replaced with a comma.

Text

Description automatically generated
  1. In the Replace dialog box, replace what is in the Find what box with a comma and what is in the Replace with box with a dot, then click the Replace All button.
Graphical user interface, text, application, email

Description automatically generated

The commas are replaced with dots.

Graphical user interface, text

Description automatically generated

We now have to replace the question mark placeholders with commas.

  1. In the Replace dialog box, replace what is in the Find what box with a question mark (?) and what is in the Replace with box with a comma, then click the Replace All button.
Graphical user interface, text, application, email

Description automatically generated

Click the Cancel button to dismiss the Replace dialog box.

The question marks are replaced with commas. The commas have been changed to dots, and the dots have been changed to commas.

Text

Description automatically generated
  1. Press Ctrl + A to select the dataset and Ctrl + C to copy it.
Graphical user interface, text

Description automatically generated
  1. Open a new worksheet and press Ctrl + V to paste the dataset.
Graphical user interface, application, table, Excel

Description automatically generated
  1. Format the dataset to make it appealing.
Table, Excel

Description automatically generated

Notice that the values in column C are left-aligned because they are text strings and, therefore, cannot be used in calculations.

Method #4: Use Excel Flash Fill Feature

This method applies the Excel Flash Fill Feature to change the commas in a dataset to dots and vice versa. The Flash Fill feature fills in data automatically once it detects a pattern in the data.

We use the following dataset in our demonstration. Our goal is to change the comma separator to a dot and the dot separator to a comma using the Flash Fill feature.

Graphical user interface, application, table, Excel

Description automatically generated

We use the following steps:

  1. Select cell D3 and enter the value 2.000,45. Select cell D4 and enter the value 1.500,52 as shown below:

Notice that Excel senses a pattern in the data and suggests the next values in grey.

  1. Press Enter to accept the suggested values if they are correct.
Table, Excel

Description automatically generated

Note: If the Flash Fill feature does not show suggested values, select cell D5 after you have entered the two values, then do either of the following:

Press Ctrl + E

Or

In the Data tab, select Flash Fill in the Data Tools group.

The Flash Fill feature senses a pattern in the first two entries you entered and fills in the rest of the data.

Method #5: Use the SUBSTITUTE and FIXED Functions

This method uses the SUBSTITUTE and FIXED functions to change commas to decimal points and decimal points to comas.

The SUBSTITUTE function substitutes a new text string for existing text in a text string. The FIXED function rounds a number to the specified number of decimals and returns the result as text with or without commas.

In our illustration, we use the following example dataset: commas separating groups of thousands and decimal points separating decimals.

Table, Excel

Description automatically generated

We want to change the commas to dots and the dots to commas.

We use the following steps:

  1. Enter the following formula in cell D3:
  1. Click the Enter button on the formula bar to enter the formula.
Graphical user interface, application

Description automatically generated
  1. Drag down or double-click the fill handle to copy the formula down the column.
Table, Excel

Description automatically generated

Explanation of the formula

  • SUBSTITUTE(FIXED(C3,2,FALSE),”.”,”$”) The innermost SUBSTITUTE function replaces the dots with dollar ($) signs.
Graphical user interface, application, table, Excel

Description automatically generated
  • SUBSTITUTE(SUBSTITUTE(FIXED(C3,2,FALSE),”.”,”$”),”,”,”.”) The second innermost SUBSTITUTE function replaces the commas with dots.
  • =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FIXED(C3,2,FALSE),”.”,”$”),”,”,”.”),”$”,”,”) The outermost SUBSTITUTE function replaces the dollar symbol ($) with a comma. The commas have been changed to dots and dots to commas.

Conclusion

This tutorial showed five techniques for changing commas to dots and vice versa in Excel. We hope you found the tutorial helpful.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.