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

Dataset Overview

Assume we have a dataset containing a list of fruit, country and the sales amount.

Method 1 – Using Excel Options Dialog Box

• Arrange Your Dataset
  • Organize 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 and select 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.

• • 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 Input:
  • 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, choose Editing and select 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 and select Text to Columns.

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

• Advanced Options
  • Skip Step 2 of 3 by clicking Next.

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

• • In the Advanced Text Import Settings window, enter a comma (,) in the Decimal separator field.
  • Click on OK.

• • Click on the Finish.

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

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