How to Change Number Format from Comma to Dot in Excel (5 Ways)

In a lot of cases, we have seen that Excel uses Comma (,) as a 1000 Separator and this is practical in many countries. However, some countries in the world use Period (.) as a 1000 separator in their number format. In this article, we’ll show you the effective ways to Change Number Format from Comma to Dot in Excel.

To illustrate, we’re going to use a sample dataset as an example. For instance, the following dataset represents the Salesman, Product, and Net Sales of a company. In the Net SalesComma is used as a 1000 Separator.

change excel number format comma to dot


Download Practice Workbook

Download the following workbook to practice by yourself.


5 Ways to Change Number Format from Comma to Dot in Excel

1. Change Number Format from Comma to Dot with Excel File Options

We know in Excel, we can change or edit different settings in the file options. In our first method, we’ll simply use the Excel File Options to Change the Number Format from Comma to Dot. Therefore, follow the steps below to perform the task.

STEPS:

  • First, go to File Options.
  • As a result, the Excel Options dialog box will pop out.
  • Then, in the Advanced tab, under the Editing options, uncheck Use system separators and type ‘,’ in the Thousands separator box.
  • After that, press OK.

Change Number Format from Comma to Dot with Excel File Options

  • Finally, you’ll see that the Comma is replaced by Dot in your worksheet.

Read More: Excel Custom Number Format Multiple Conditions


2. Convert Number Format from Comma to Dot in Excel Through Notepad

We will use Windows Notepad to find and replace the comma in our Excel worksheet. So, learn the below steps to make the changes.

STEPS:

  • Firstly, copy the range B4:D9.

Convert Number Format from Comma to Dot in Excel Through Notepad

  • After that, paste it into Notepad.

Convert Number Format from Comma to Dot in Excel Through Notepad

  • Subsequently, under the Edit tab, select Replace.

Convert Number Format from Comma to Dot in Excel Through Notepad

  • Consequently, the Replace dialog box will pop out.
  • Type ‘.’ in the Find what box and type ‘,’ in the Replace with box.
  • Afterward, select Replace All.

  • Lastly, it’ll return the Net Sales making the required changes.

Read More: How to Format Number with VBA in Excel (3 Methods)


3. Use ‘Find and Replace’ Feature to Change Number Format from Comma to Dot

Additionally, we can use the ‘Find and Replace’ feature in Excel to convert the number format. Hence, follow the process given below to Change the Number Format from Comma to Dot in Excel.

STEPS:

  • First of all, change the Net Sales format from Number to Text.

Use ‘Find and Replace’ Feature to Change Number Format from Comma to Dot

  • Now, manually input Comma as 1000 separator in the Net Sales.
  • Next, select the range D5:D9.

Use ‘Find and Replace’ Feature to Change Number Format from Comma to Dot

  • Then, go to Home Editing Find & Select Replace.

Use ‘Find and Replace’ Feature to Change Number Format from Comma to Dot

  • As a result, the Find and Replace dialog box will pop out.
  • After that, type ‘.’ in the Find what box and type ‘,’ in the Replace with box.
  • subsequently, select Replace All.

  • Finally, change the format to Number back again and it’ll return the expected outcome.

Related Content: Custom Number Format: Millions with One Decimal in Excel (6 Ways)


Similar Readings:


4. Temporary Change in Windows System Settings to Convert Number Format from Comma to Dot in Excel

Moreover, we can change the Windows System Settings on our PC to convert the number format from Comma to Dot in Excel. Therefore, learn the following process to carry out the operation.

STEPS:

  • In the beginning, select Start Control Panel Clock and RegionRegion.
  • Consequently, the Region dialog box will appear. There, select Additional settings.

Temporary Change in Windows System Settings to Convert Number Format from Comma to Dot in Excel

  • As a result, the Customize Format dialog will pop out.
  • Under the Numbers tab, select ‘.’ in the Digit grouping symbol and press Apply.

Temporary Change in Windows System Settings to Convert Number Format from Comma to Dot in Excel

  • In the end, the ‘.’ will act as a 1000 Separator in your Excel worksheet and in other applications of your PC as well.

Read More: How to Apply Number Format in Millions with Comma in Excel (5 Ways)


5. Apply NUMBERVALUE Function for Changing Number Format from Comma to Dot

The NUMBERVALUE function in Excel converts a certain text to a number in a locale-independent manner. In our last method, we’ll use this function to change the number format. Hence, follow along to perform the task.

STEPS:

  • Firstly, change the Net Sales Number format to Text format.

Apply NUMBERVALUE Function for Changing Number Format from Comma to Dot

  • Then, manually input the Comma as a 1000 Separator.

Apply NUMBERVALUE Function for Changing Number Format from Comma to Dot

  • After that, select E5 and type the formula:
=NUMBERVALUE(D5,",",".")

  • Next, press Enter and use the AutoFill tool to convert the rest.
  • At last, you’ll see the desired changes.

Read More: How to Format a Number in Thousands K and Millions M in Excel (4 Ways)


Conclusion

Henceforth, you will be able to Change Number Format from Comma to Dot in Excel with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo