How to Change Commas to Decimal Points in Excel? 5 Easy Ways!

Countries such as the USA and the UK use a comma to separate groups of thousands and a decimal point to separate decimals.

Many other countries such as Spain and Denmark use a decimal point to separate groups of thousands and a comma to separate decimals. 

Therefore, sometimes you may need to change commas to decimal points in Excel to display numbers in a format that suits your audience.

In this tutorial, I will show you five techniques for changing commas to decimal points in Excel. It also shows how to change decimal points to commas in Excel.

Method #1: Temporarily Replace System Separators with Custom Separators in Excel

In this method, we use the Excel Options dialog box to change the options relating to the thousands and decimal separators.

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

Dataset with numbers with comma

Our goal is to change the commas to decimals and the decimals to commas. 

We use the following steps:

  1. Select File to open the Backstage screen.
Click the File tab
  1. Select Options on the left sidebar of the Backstage screen. 
Select options
  1. Select Advanced on the left sidebar of the Excel Options dialog box.
Select Advanced in Excel Options
  1. In the Editing Options section, uncheck the Use system separators option and replace what is in the Decimal separator text box with a comma (,) and what is in the Thousands separator text box with a decimal point (.).
Change decimal and thousand separators
  1. Click OK.

In our example dataset, the commas are replaced with decimal points and the decimal points are replaced with commas. 

Comma changed to decimals

Note: If you want Excel to revert to using system separators, open the Excel Options dialog box and check the Use system separators checkbox.

Method #2: Use the Replace Feature of Windows Notepad

In this method, we first copy the data from Excel and paste it into Notepad.

We then make the desired changes in Notepad and then copy the data and paste it back into Excel.

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

Dataset with commas

Our objective is to change the commas to decimals and the decimals to commas.

We use the following steps:

  1. Select the dataset and press Ctrl + C to copy it.
Copy the data
  1. From the Windows search box, search and open the Notepad App.
Search for Notepad
  1. Press Ctrl + V to paste the dataset in Notepad.
Paste the data in notepad
  1. Open Notepad’s Replace dialog box using any of the following ways:

Press Ctrl + H

Or

Select Replace on the Edit menu.

Click on replace option in notepad
  1. In the Replace dialog box, type a decimal point in the Find what text box and asterisk (*) in the Repace with text box, then click the Replace All button. 
Replace decimal with star

Note: The stars are temporary placeholders that shall be replaced with commas.

decimal replaced with asterisk
  1. In the Replace dialog box that is still open, replace what is in the Find what text box with a comma and what is in the Replace with text field with a decimal point, then click the Replace All button.
Replace comma with decimal

The commas are replaced with decimal points.

commas are replaced with decimal points

It is now time to replace the star placeholders with commas.

  1. In the Replace dialog box, replace what is in the Find what text box with asterisk (*) and what is in the Replace with text box with a comma, then click the Replace All button.
Replace asterisk with comma

Dismiss the Replace dialog box by clicking the Cancel button.

The stars are replaced with commas. We now have the currency values in the format we want.

Comma and decimal have been interchanged
  1. Press Ctrl + A to select the whole dataset and press Ctrl + C to copy it.
Copy the data in the notepad
  1. Open a new worksheet and press Ctrl + V to paste the dataset.
Paste the data in Excel
  1. Increase the column widths and bold the column headers to make the data more presentable.
Increase column width

Note: The values in column C are left-aligned because they are text strings.

Method #3: Change the Windows Regional Settings

In this method, we change the Windows regional settings to change the system separators used by Excel. 

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

Dataset

Our objective is to change the commas to decimals and the decimals to commas.

We use the steps below:

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

Open your Excel dataset and see that commas have been changed to decimal points and decimal points have been changed to commas.

Comma replaced with decimal

Note: The decimal point symbol now becomes your computer’s default separator for thousands and the comma symbol is the separator for decimals. Not only for Excel but all other applications.

If you want to restore your system’s original default separators navigate to the Customize Format dialog box as explained earlier in this tutorial and click the Reset button in the Numbers tab. 

Reset the settings

Select Yes in the message box that pops up.

Click on Yes

Click OK in the Customize Format dialog box. Click Apply and OK in the Region dialog box. 

Your system’s default settings for numbers are restored.

Method #4: Use the Combination of SUBSTITUTE and FIXED Functions

In this method, we use the SUBSTITUTE and FIXED functions to change commas to decimal points and decimal points to commas.

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 that has commas separating groups of thousands and decimal points separating decimals.  

Dataset with numbers having comma

We use the following steps:

  1. Enter the following formula in cell D2:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FIXED(C2,2,FALSE),".","$"),",","."),"$",",")
SUBSTITUTE and FIXED formula
  1. Double-click or drag down the fill handle to copy the formula down the column.
Enter the formula in all the cells

Explanation of the formula

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FIXED(C2,2,FALSE),”.”,”$”),”,”,”.”),”$”,”,”)

  • SUBSTITUTE(FIXED(C2,2,FALSE),”.”,”$”) – The innermost SUBSTITUTE function replaces the decimal points with dollar ($) signs.
Decimal replaced with dollar sign
  • SUBSTITUTE(SUBSTITUTE(FIXED(C2,2,FALSE),”.”,”$”),”,”,”.”)   The second innermost SUBSTITUTE function replaces the commas with decimal points.
Replaced comma with decimal
  • =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FIXED(C2,2,FALSE),”.”,”$”),”,”,”.”),”$”,”,”) – The outermost SUBSTITUTE function replaces the dollar symbol ($) with a comma, completing the process of changing commas to decimal points and decimal points to commas.

Method #5: Use Excel Flash Fill Feature

In this method, we use the Excel Flash Fill Feature to change the commas in a dataset to decimal points and vice versa. The Flash Fill feature automatically fills in data once it detects a pattern.

We use the following dataset in our illustration. Our objective is to change the comma separator to a decimal point and the decimal point separator to a comma using the Flash Fill feature.

Dataset

We use the following steps:

  1. Select cell D2 and enter the value 1.000,15. Select cell D3 and enter the value 2.000,23 as shown below:
Enter expected results in adjacent columns

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

  1. Press Enter to accept the suggested values if they are correct. 
Press enter

Note: If the Flash Fill feature does not show suggested values, select cell D4 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.

Flash fill

The Flash Fill feature detects a pattern in the first two values you entered and fills in the rest of the values.

In this tutorial, we have looked at 5 techniques for changing commas to decimal points and decimal points to commas in a dataset.

You may need to do this to display numbers in a format that suits certain audiences.

Other articles you may also like: