How to Change Comma to Dot in Excel (4 Methods)

Method 1-Using Notepad to Replace Commas with Dots

Sample Dataset Overview:

  • Imagine a dataset representing a company’s sales records.
  • The dataset includes columns for Salesman, Vehicle, and Unit Price.
  • Commas are used as the 1000 separator in the Unit Price.

 

a sample dataset :how to change comma to dot in excel

Steps:

  • Open Notepad by typing Notepad in the Windows search box.

Type Notepad in the search box

  • Paste the data range from Excel into Notepad.

Paste the data range from Excel to Notepad

  • Go to Edit >> Replace.

Go to Edit and Replace

  • In the Find What box, type a comma (,).
  • In the Replace With box, type a dot (.) and click Replace All.

Replace Comma With Dot

  • The commas will be replaced with dots in Notepad.

Dot replaced Comma

  • Finally, copy the data from Notepad and paste it back into Excel.

Copy the data from notepad and paste it in the Excel

Read More: How to Change Decimals to Percentages in Excel


Method 2 -Temporarily Changing Excel Settings

Steps:

  • Go to File >> Options >> Advanced.
  • Under Editing options, uncheck Use system separators.
  • Remove the comma in the Decimal separator and Thousands separator fields.

Remove comma to dot in Excel

  • The data range will now display dots instead of commas.

The data range

Read More: How to Change International Number Format in Excel


Method 3 -Temporarily Changing Windows System Settings

Furthermore, we may alter the Windows System Settings on our PC to convert the number format in Excel from Comma to Dot. Learn the following procedure to carry out the operation.

Steps:

  • Click the Windows button and navigate to the Windows System folder >> Control Panel.

Open Control Panel

  • Follow this path: Control Panel >> Clock and Region >> Region.
  • Click on Additional Settings in the Formats tab.

Click on the Additional Settings

  • Customize the format by going to Numbers >> Digit grouping symbol >> Apply.

Applying Digit grouping symbol

  • The data range will now show dots instead of commas.

The data range


Method 4 – Using Excel’s Find and Replace Command

Overview:

  • In this method, we’ll utilize Excel’s Find and Replace tool to transform the number format from commas to dots.
  • Suppose someone has copied data from a spreadsheet and wants to paste it into Excel.

Steps:

  • Right-click the mouse and choose Paste Special.
  • Select Paste >> Unicode Text >> OK.

Paste Special as Unicode Text

  • The data will appear as shown in the image below because it was pasted as Unicode Text.

Pasted data as Unicode Text

  • Now follow these steps:
    • Go to the “Home” tab.
    • Click Editing >> Find & Select >> Replace (or press Ctrl+H).

Applying Find & Select

  • The Find and Replace window will appear.
  • In the Find what box, type a comma (,).
  • In the Replace with box, type a dot (.) and click OK.

Find comma and Replace with dot

  • Microsoft Excel will perform the replacement, and you’ll see the updated numbers.

A total replacement number

  • The following image illustrates the process where the comma is replaced by a dot.

Image represents a process where the comma replaced by a dot


Download Practice Workbook

You can download the practice workbook from here:


 

Related Articles


<< Go Back to Change Number Format | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Al Ikram Amit
Al Ikram Amit

Al Ikram Amit, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a key Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Amit not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently delivering exceptional, high-quality content that adds significant... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo