How to Change Decimal Separator in Excel (7 Quick Methods)

In this article, we will learn how to change the decimal separator in Excel. This tutorial will guide you with some quick methods to do so. We will describe the steps of the methods with screenshots so that you can understand the process easily. Let’s see the methods below.

Introduction to Decimal Separator

We use the decimal separator to separate the integer and fractional parts of a number. It is usually expressed with the dot (.) symbol. Besides, some countries use comma (,) as the decimal separator. This separator is also known as the radix character.

Here, we will demonstrate seven quick approaches to changing the decimal separator in Excel. For this, we will use the dataset (B4:D9) below containing the names of some Fruits, the Countries that ordered them, and the Sales. We can see that the Sales (D5:D9) contain decimal separators. In this tutorial, we will show you the process to change two types of decimal separators: dot (.) and comma (,). In the first five methods, we will learn to replace dots with commas. Then, in the last two methods, we will do the opposite. So, without further delay, let’s get started.

1. Changing Decimal Separator Using Excel Options Dialog Box

In this method, we will change the decimal separator using the Excel Options dialog box. In the dataset (B4:D9) below we can see that the Sales (D5:D9) values contain dots (.) as the decimal separator. However, we want to use commas (,) instead of the dots (.) as the decimal separator. The steps to do so are below.

Steps:

Firstly, go to the File tab.

Secondly, click on the Options button.

In turn, the Excel Options dialog box will pop up.
Therefore, click on the Advanced option located on the left side of the dialog box.
Finally, click OK.

Thus, you can convert the decimal points (.) into commas (,).
See the final output in the following screenshot.

2. Using Text Editor to Change Decimal Separator in Excel

In this method, we will take the help of a text editor (Notepad) to change the decimal separator in Excel. Here, we will use the same dataset as the previous method. Follow the steps below to use the Notepad for replacing decimal points (.) with commas (,).

Steps:

In the beginning, you have to open Windows Notepad.
To open it, click on the Search bar below.

Next, start typing the name of the text editor (Notepad).
Hence, you will see the Notepad option in the list ( see screenshot).
Now, click on Notepad.

Consequently, the Notepad window will appear.
See the window in the following image.

Therefore, go to the worksheet and select the range (D5:D9) where you want to change the decimal separator (.).

After that, right-click on the selection.
Accordingly, click on the Copy button.

Then, return to the Notepad window and Paste the copied item.

As a consequence, you can see the copied range in the Notepad window of the screenshot below.

Afterward, go to the Edit tab.
Later, click on the Replace option.

In turn, the Replace window will pop up.
At this time, go to the Find what box and type ‘.’.
Subsequently, insert ‘,’ in the Replace with box.
Eventually, click on Replace All.

Ultimately, Close the window.

As a result, we can see that all the dots (.) are replaced with commas (,).

Forthwith, go to the Excel worksheet and delete the values in the D5:D9 range.

Momentarily, return to the Notepad window and Copy the values (see screenshot below).

Select cell D5 and Paste the values.

Finally, we can see the values with commas as decimal separators in the D5:D9 range of the image below.

3. Utilizing Global System Settings for Changing Decimal Separator

Let’s say we have a dataset (B4:D9) below where we can see dots (.) as decimal separators in column D. This method will show you the process to convert the dots into commas by changing global system settings that are placed in the Control Panel. The steps are below.

Steps:

First of all, click on the Search bar located at the bottom left corner of the screen.
Then, start typing ‘control’.
Eventually, you will see the Control Panel option in the list.
Therefore, click on the Control Panel.

In turn, the Control Panel window will pop up.
Now, select Clock and Region.

Accordingly, the Clock and Region window will appear.
At this time, select Region.

Consequently, the Region window will open.
Next, go to Additional Settings.

As a result, a window named Customize Format will appear.
Afterward, go to the Numbers tab.
Thereupon, type ‘,’ in the Decimal symbol box.
Ultimately, click OK.

Again, click OK in the Region window.

Finally, you will see that the dots will be converted to commas in the D5:D9 range.
See the final output in the following picture.

Note:

This method will update the default settings of the device. That means, it will change the decimal separator of all the Excel workbooks along with other programs.

4. Changing the Decimal Separator with the Help of the Flash Fill Tool in Excel

We can also change the decimal separator using the Flash Fill technique in Excel. This technique can recognize a pattern in a text and you can format part of a text in a new column. To demonstrate this approach, we will use the same dataset (B4:E9) as the previous methods. But here, we will need to add an extra column named Formatted Sales ($) to insert the Sales values (D5:D9) with commas. The steps to apply the Flash Fill tool are below.

Steps:

To begin, go to cell E5.
Next, type the value (3205.75) in cell D5 with a comma (,) as the decimal separator (3205,75).
Then, press the Enter key.
See the screenshot below.

After that, type the first number (1) of the next value (1508.15) in cell E6.
Consequently, you will see the suggestions in the range E6:E9.
However, all the suggestions will show commas as decimal separators (see screenshot).

Therefore, press the Enter button to accept the suggestions.
In this way, you can change the decimal separators easily.
See the final result in the image below.

Read More: Excel Decimal Places Problem

5. Alternate Decimal Separator with Find and Replace Features

In this approach, we will demonstrate the process to alternate the decimal separator with the Find and Replace features in Excel. It is located at the Home tab and used to find and replace any number, text or character. To describe this method, we will use the same dataset that we used in the first three methods and change the dots with commas. The steps are below.

Steps:

In the first place, select the range (D5:D9) where you want to apply the Find & Replace features.
Afterward, go to the Home tab.

Then, click on the Find & Select dropdown in the Editing group.

Next, select Replace from the dropdown menu.

In turn, the Find and Replace window will pop up.
Therefore, go to the Replace tab.
After that, type ‘.’ in the Find what box.
Subsequently, go to the Replace with box and insert ‘,’.
Eventually, click on the Replace All button to replace all the dots with commas.

Hence, a message box (Microsoft Excel) will appear and show the number of replacements (5).
Click OK.

In this way, you can replace all the dots with commas in the D5:D9 range (see screenshot).

Read More: How to Add Decimals in Excel

6. Assigning Excel NUMBERVALUE Function to Convert Decimal Separator

Let’s say, we have a dataset below (B4:E9). Here, we can see the Sales values (D5:D9) with commas as decimal separators. In this approach, we will apply the NUMBERVALUE function in Excel to replace these commas with dots. This function turns a text into a number by defining the decimal and group separators. The steps are below.

Steps:

First, go to cell E5.
Next, to replace the comma (,) of the value in cell D5 the with a dot (.), type the following formula in cell E5:

=NUMBERVALUE(D5,",",".")

Therefore, press the Enter button.
Eventually, you will see the value of cell D5 with a dot (.) as the decimal separator in cell E5.

Now, to copy the formula in the rest of the cells (E6:E9), put your cursor in the bottom right corner of cell E5.
Hence, you will see a plus sign (+) that is called the fill handle.

Thereupon, double-click on the plus sign.
After that, the formula will be applied to the range E6:E9.
See the final output in the following picture.

7. Changing Decimal Separator with Text to Columns Feature

Suppose, we have a dataset (B4:D9) below that contains the Sales (D5:D9) values with commas as decimal separators. Here, we will apply the Text to Columns tool in Excel to convert the commas into dots. This tool is located in the Data tab. It is mainly used to split the text into multiple columns, But, in this method, we will use it to change the decimal separator. Follow the steps below to do so.

Steps:

To begin, select the range D5:D9.
Then, go to the Data tab.

After that, click on Text to Columns in the Data Tools group.

In turn, the window of Step 1 of 3 will appear.
Therefore, go to the Choose the file type that best describes your data section.
Next, select Fixed width.
Click Next.

At this time, click Next in Step 2 of 3 window to skip this step.

Thereupon, you will see the Step 3 of 3 window.
Click on Advanced.

As a result, the Advanced Text Import Settings window will pop up.
Consequently, enter comma (,) in the Decimal separator box.
Click OK.

Finally, click on the Finish button in Step 3 of 3 window.

Thus, you can change all the decimal separators in the D5:D9 range (see the screenshot below).

Download Practice Workbook

Download the practice workbook from here.

Changing Decimal Separator.xlsx

Conclusion

I hope the above tutorial will be helpful for you to change the decimal separator in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section.