How to Change the Comma Separator in Excel (7 Easy Methods)

Dataset Overview

When working with numbers in Excel, it’s essential to understand how to handle decimal separators. While many countries use the dot (.) as the decimal separator, others use the comma (,). In this guide, we’ll explore seven easy methods to change the decimal separator in Excel.

Method 1 – Using Excel Options Dialog Box

• Arrange Your Dataset
  • Start by organizing your dataset. For example, consider the dataset shown below:

• Format Cells
  • Right-click on the data and select Format Cells.

• • In the Format Cells dialog box, choose the Number category.
  • Check the Use Separator option and click OK.

• Change Thousand Separators
  • Go to the File menu.

• • Select Options from the More section.

• • In the Excel Options dialog box, navigate to Advanced > Thousand Separators.
  • Change the comma to a dot (or vice versa) and click OK.

• Result:
  • Your dataset will now display numbers with the desired decimal separator.

Method 2 – Use a Text Editor

• Open Notepad
  • Search for Notepad in the Windows search bar and open it.

• Copy Data
  • Select the data range you want to modify in Excel.
  • Copy the entire column using Ctrl+C.

• Paste into Notepad
  • Paste the copied data into Notepad using Ctrl+V.

• Replace Commas with Dots
  • In Notepad, go to Edit > Replace

• • In the Find what box, enter a comma (,).
  • In the Replace with box, enter a dot (.) and click Replace All.

• You will see the numbers with dots.

• Copy Back to Excel
  • Copy the modified numbers from Notepad.
  • Paste them back into your Excel file.

• Final Result
  • Your dataset will now have the desired decimal separator.

Method 3 – Utilizing Global System Settings

• Open Control Panel
  • Manually search for Control Panel in the Windows search bar.
  • Select the Control Panel option.

• Access Clock and Region Settings
  • In the Control Panel dialog box, choose Clock and Region.

• • Then select Region.

• Customize Format
  • Navigate to the Advanced Settings option.

• • In the Customize Format dialog box, make the desired changes (from comma to dot) and click OK.

• Apply Changes
  • Confirm the changes by clicking OK again.

• Your system-wide decimal separator will now be updated.

Method 4 – Comma Separator Changing with Flash Fill Tool

• Create an Extra Column
  • Add an extra column (e.g., Formatted Sales ($)) next to your dataset.

• Manual Inpu:
  • Enter the numbers manually with a dot separator in this new column.

• • The Flash Fill tool will automatically recognize the pattern and fill in the remaining cells.

• Press Enter
  • Press Enter after typing the second number.

Method 5 – Using Find and Replace Features

• Select Data
  • Highlight the data range.
  • Go to the Home tab > Editing > Find and Select.

• Find and Replace
  • Choose Replace.

• • In the Find what box, enter a comma (,).
  • In the Replace with box, enter a dot (.) and click Replace All.

Your data will contain the desired dot separator.

Method 6 – Use of NUMBERVALUE Function

Apply Formula

• Insert the following formula in cell E5:

• Fill Handle
  • After getting the result for E5, use the Fill Handle to apply the formula to other cells.

Your data will contain the desired dot separator.

Method 7 – Applying Text to Columns Feature

• Select Data
  • Highlight the dataset.

• Text to Columns
  • Go to Data > Text to Columns.

• • In Step 1 of 3, choose Fixed width and click Next.

• Advanced Options
  • Skip Step 2 of 3.

• • In Step 3 of 3, click on Advanced.

• • In the Advanced Text Import Settings window, enter a comma (,) in the appropriate field.

• • Click on the Finish button in Step 3 of 3.

• Final Result
  • Your dataset will now have the desired decimal separator.

How to Change Decimal Separator in Excel Using Flash Fill

• Arrange Your Dataset
  • Start by organizing your dataset. For example, consider the dataset shown below:

• Manual Input
  • Enter the numbers manually with a dot separator in a new column (e.g., Formatted Sales ($)).

• As you type, the Flash Fill option will automatically recognize the pattern and fill in the remaining cells.

• Result
  • Press Enter after typing the second number.
  • The Flash Fill will complete the conversion for all relevant cells, giving you the proper result.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• How to Remove Comma in Excel Using Formula
• Remove Comma from Currency in Excel
• How to Remove Commas in Excel from CSV File
• How to Remove Inverted Comma in Excel

<< Go Back To Remove Comma in Excel | Data Cleaning in Excel | Learn Excel