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

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.

Dataset to Change Comma Separators 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.

Right-click to Change Comma Separators in Excel

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

Using Format cell box to Change Comma Separators in Excel

  • 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.

Adavanced dialog box to Change Comma Separators in Excel

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

Final Result to Change Comma Separators in Excel


Method 2 – Use a Text Editor:

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

Openening Notepad to Change Comma Separators in Excel

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

Copying to Change Comma Separators in Excel

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

Paste into Notepad to Change Comma Separators in Excel

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

Using Find and Replace Option to Change Comma Separators in Excel

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

Using Find and Replace Dialog box to Change Comma Separators in Excel

  • You will see the numbers with dots.

Result to Change Comma Separators in Excel

  • 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.

Final Result to Change Comma Separators in Excel


Method 3 – Utilizing Global System Settings:

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

Using Control Panel Option to Change Comma Separators in Excel

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

Selecting Clock and Region Option to Change Comma Separators in Excel

    • Then select Region.

Select the Region Option to Change Comma Separators in Excel

  • 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.

Final Result to Change Comma Separators in Excel


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:
    • Type the numbers manually with a dot separator in this new column.

Type the desired number with dots manually to Change Comma Separators in Excel

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

Using Flash Fill Option to Change Comma Separators in Excel

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

Final Result to Change Comma Separators in Excel


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:
=NUMBERVALUE(D5,",",".")

Inserting Formula to Change Comma Separators in Excel

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

Using Fill Handle to Change Comma Separators in Excel

Your data will contain the desired dot separator.

Final Result to Change Comma Separators in Excel


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:
    • Type 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

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

Get FREE Advanced Excel Exercises with Solutions!
Zehad Rian Jim
Zehad Rian Jim

Zehad Rian Jim is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He's good with Microsoft Office and has written more than 80 helpful articles for ExcelDemy in the last year. Now, he makes fun and easy-to-follow Excel tutorials for YouTube as part of the ExcelDemy Video project. Zehad loves figuring out Excel problems, and his passion for learning new things in Excel shines through in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo