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

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.


Download Practice Workbook

You can download the practice workbook from here.


7 Easy Methods to Change Comma Separator in Excel

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

Dataset to Change Comma Separators in Excel


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.

Right-click to Change Comma Separators in Excel

  • After that, in the Format cell dialog box, use the Number option and then tick the Use Separator option and press OK.

Using Format cell box to Change Comma Separators in Excel

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

Adavanced dialog box to Change Comma Separators in Excel

  • Finally, you will get the final result.

Final Result to Change Comma Separators in Excel

Read More: How to Remove Comma in Excel Using Formula (2 Ways)


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.

Openening Notepad to Change Comma Separators in Excel

  • Second, select the data range you want to use and then copy the whole column by using the Ctrl+C buttons.

Copying to Change Comma Separators in Excel

  • Third, paste the copied data into the notepad by pressing the Ctrl+V buttons.

Paste into Notepad to Change Comma Separators in Excel

  • Forth, go to Edit > Replace options in the notepad.

Using Find and Replace Option to Change Comma Separators in Excel

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

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

  • Sixth, you will see the numbers with dots.

Result to Change Comma Separators in Excel

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

Final Result to Change Comma Separators in Excel


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.

Using Control Panel Option to Change Comma Separators in Excel

  • In addition, in the Control Panel dialog box, select the Clock and Region option.

Selecting Clock and Region Option to Change Comma Separators in Excel

  • Furthermore, select the Region option.

Select the Region Option to Change Comma Separators in Excel

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

Final Result to Change Comma Separators in Excel


4. Comma Separator Changing with Flash Fill Tool

In addition, we will try to change the comma separator in excel 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.

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

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

Using Flash Fill Option to Change Comma Separators in Excel

  • Lastly, press Enter button and you will get the proper result accordingly.

Final Result to Change Comma Separators in Excel


5. Using Find and Replace Features

Moreover, our aim is to change the comma separator in excel by using the Excel Options 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,",",".")

Inserting Formula to Change Comma Separators in Excel

  • Second, after getting the result for this cell, use the Fill Handle option to execute the formula for every cell.

Using Fill Handle to Change Comma Separators in Excel

  • Last, you will get the proper result accordingly.

Final Result to Change Comma Separators in Excel

Read More: How to Remove Comma Between Numbers in Excel


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.


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. Follow the ExcelDemy website for more articles like this. 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

Zehad Rian Jim

Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo