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:
=NUMBERVALUE(D5,",",".")
- 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
Get FREE Advanced Excel Exercises with Solutions!