This tutorial will demonstrate how to change the comma separator in Excel. In our day-to-day life, we have to deal with lots of data. When we are upstaging with sales data or any data related to price, we often use a separator to read the value easily. The most used separator in Excel is a comma. But any many cases, using lots of commas can create puzzlement for viewers or readers. So, it is very conspicuous to learn how to change the comma separator in Excel.
How to Change Comma Separator in Excel: 7 Easy Methods
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. We will use a sample dataset overview as an example in Excel to understand easily. In most of the examples, we will try to use this dataset. If you follow the steps correctly, you should learn to change the comma separator in excel. The methods are
1. Using Excel Options Dialog Box
In this case, our goal is to change the comma separator in Excel by using the Excel Options Dialog Box in Excel. 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. At first, we will learn to change the general number into a number with a comma separator and then we will change the comma number into a dot separator again. The steps to do so are below.
Steps:
- First, arrange a dataset like the below image.
- Next, right-click on the data and choose the Format cell option.
- After that, in the Format cell dialog box, use the Number option and then tick the Use Separator option and press OK.
- Moreover, you will get the dataset with comma separator numbers.
- In addition, go to the File option.
- Afterward, select the Options from More option.
- Next, in the Excel Options dialog box, go to Advanced >Thousand Separators Then, change the comma into a dot option and press OK.
- Finally, you will get the final result.
2. Use of Text Editor
Now, we want to change the comma separator in Excel by using the Text Editor Option in Excel. We will take the help of a text editor (Notepad) for changing the decimal separator in Excel. Here, we will use the dataset described at the very beginning of the article. Follow the steps below to use the Notepad for replacing commas (,) with decimal points (.).
Steps:
- In the beginning, you have to open Windows Notepad, and to open it, click on the Search bar below. Next, start typing the name of the text editor (Notepad) and you will see the Notepad option in the list ( see screenshot). Now, click on Notepad and the Notepad window will appear.
- Second, select the data range you want to use and then copy the whole column by using the Ctrl+C buttons.
- Third, paste the copied data into the notepad by pressing the Ctrl+V buttons.
- Forth, go to Edit > Replace options in the notepad.
- Fifth, in Find, what box select the comma option, and in Replace with box select, the dot option and press Replace All option to execute the change.
- Sixth, you will see the numbers with dots.
- Seventh, again copy the numbers from notepad and paste them into our excel file by using the previous step.
- Last, you will get the desired result.
3. Utilizing Global System Settings
Next, we can change the comma separator in excel by using the Excel Options Dialog Box in excel. This method will show you the process to convert the commas into dots by changing global system settings that are placed in the Control Panel. The steps are below.
Steps:
- To begin with, in the window search write “control panel” manually and select the Control Panel option.
- In addition, in the Control Panel dialog box, select the Clock and Region option.
- Furthermore, select the Region option.
- Then, in the Region dialog box, go to the Advanced Settings option.
- Afterward, in the Customize Format dialog box, make the desired changes from comma to dot and press OK.
- Next, again press OK to execute the changes.
- Finally, you will get the proper result accordingly.
4. Comma Separator Changing with Flash Fill Tool
In addition, we will try to change the comma separator by using the Flash Fill tool 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 dots. The steps to apply the Flash Fill tool are below.
Steps:
- First, type the number manually with a dot separator.
- Secondly, type the number manually with the dot separator again and you will see that the flash-fill option automatically shows the results for every other cell.
- Lastly, press Enter button and you will get the proper result accordingly.
5. Using Find and Replace Features
Moreover, our aim is to change the comma separator in Excel by using the Find and Replace dialog box 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 previous method and change the commas with dots. The steps are below.
Steps:
- First, select the data > Home > Editing > Find and Select options.
- Next, in Find and Select option choose the Replace option.
- After that, in Find what box select the comma option, and in Replace with box select the dot option and press Replace All option to execute the change.
- Finally, you will get the proper result accordingly.
6. Use of NUMBERVALUE Function
After that, we will use the NUMBERVALUE function to change the comma separator in Excel. 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, insert the following formula in cell E5.
=NUMBERVALUE(D5,",",".")
- Second, after getting the result for this cell, use the Fill Handle option to execute the formula for every cell.
- Last, you will get the proper result accordingly.
7. Applying Text to Columns Feature
In our last case, we will try to change the comma separator in Excel by using the Text to Columns feature in Excel. 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 with, select the data.
- In addition, go to Data> Text to Columns options.
- Furthermore, the window of Step 1 of 3 will appear. Go to the Choose the file type that best describes your data section select Fixed width and Click Next.
- After that, click Next in Step 2 of 3 windows to skip this step.
- Next, you will see Step 3 of 3 windows and click on the Advanced option.
- Moreover, the Advanced Text Import Settings window will pop up.
- Consequently, enter comma (,) in the Decimal separator box and click OK.
- Again, click on the Finish button in Step 3 of 3.
- Finally, you will get the proper result accordingly.
How to Change Decimal Separator in Excel
In this case, our goal is to change the decimal separator in Excel. We will use the Flash Fill Tool to fulfill our goal. The steps of this method are.
Steps:
- First, arrange a dataset like the below image.
- Second, type the number manually with a dot separator.
- Third, type the number manually with the dot separator again and you will see that the flash-fill option automatically shows the results for every other cell.
- Last, you will get the proper result accordingly.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
Henceforth, follow the above-described methods. Hopefully, these methods will help you to learn how to change comma separator Excel. We will be glad to know if you can execute the task in any other way. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.
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!
