5 Way to Replace Commas with Dots in Excel

In the work of the Excel program, everything functions in accordance with the prescribed formulas and functions. Even because of a single dot or comma, the entire bookkeeping can fail. And this means that it will be useful for each user of the program to learn how to quickly find the mistake made and correct it.

Replacement procedure

In the version of Excel, a comma is used to denote decimal fractions, but in the English program, dots are used. Often this error occurs due to working in two languages ​​or due to lack of knowledge.

To begin with, it is worth deciding on the reasons why it became necessary to replace the comma with a dot. In some cases, this is due to a more attractive visual display rather than functional requirements. But if the replacement requirement is dictated by the need for calculations, then the choice of a method for replacing commas with dots should be taken seriously. Depending on the purpose of the replacement, the method will be different.

Method 1: Use the Find and Replace Tool

One of the simplest and best known methods for replacing a comma with a dot is to use a tool called Find and Replace. Unfortunately, this method is not suitable for functional fractions. When replacing a comma with a dot using this method, the cell values ​​will be transformed into text format. Consider the mechanism of the Find and Replace method:

1. We select a specific range of cells that need to be replaced. When you right-click on the selected area, a menu pops up. Here we select the item called “Format Cells”. This function can be called by the keyboard shortcut Ctrl+1.
2. When “Format Cells” is activated, a formatting window opens. In the “Number” parameter, select the “Text” criterion. To save the changes made, be sure to click “OK”. If you just close the formatting window, then all changes will lose their effect.
3. Let’s move on to the next step. Again, select the required number of cells. In the active tab “Home” we find the block of functions “Editing”, select “Find and select”. In the menu that appears after this, the “Replace” option should be activated.

4. Next, a window called “Find and Replace” opens to fill in two “Find” parameters – a character, word or number is entered, and in “Replace with” you should specify the character, word or number to which the replacement will be made. Thus, in the line “Find” there will be a “,” symbol, and in the line “Replace with” – “.”.
5. After filling in the parameters, click “Replace All”. After that, a small message will appear about the number of replacements made. Click “OK”.

This method allows you to replace all commas with periods in the selected area of ​​cells. The procedure is simple and fast. The disadvantage of this method is the replacement of the format with a text one, which excludes any further calculations.

Method 2: Use the SUBSTITUTE Function

The method is based on the use of the corresponding function of the same name. When choosing this method, it is necessary to convert the cell data, and then copy it and paste it in place of the original data.

1. By selecting an empty cell, next to the cell that is subject to change. Activate “Insert function” – the symbol in the line of functions “fx”.
2. In the window that appears with the available functions, we find the “Text” subsection. Select the formula called “Substitute” and save the selection by pressing the “OK” button.

3. A window appears for filling in the required parameters – “Text”, “Old text” and “New text”. The “Text” parameter involves entering the address of the cell with the original value. The line “Old text” is intended to indicate the character to be replaced, that is, “,”, and in the “New text” parameter we enter “.” When all parameters are filled in, click OK. The following will appear in the active cell: =SUBSTITUTE(C4; “,”; “.”).

4. As a result, the cell value is changed successfully. These manipulations should be repeated for all other cells.
5. This method also has a significant disadvantage. If only a few values ​​are to be replaced, then repeating the steps will not take much time. But what if you need to change a fairly large array of data. You can, for example, use the cell fill marker.
6. To implement this item, you must set the cursor to the lower right corner of the active cell with the already entered function. In this case, a cross will appear – the so-called fill marker. By clicking on the left mouse button, you should drag this cross along the column with the values ​​that need to be changed.
7. As a result, already changed values ​​will appear in the selected column – instead of commas in decimal fractions, now there are dots. Now you need to copy and transfer all the obtained transformed values ​​​​to the cells of the original numbers. Highlight changed cells. Click the “Copy” button in the “Main” tab.
8. When you right-click the computer mouse on the selected cells, a menu appears with the category “Paste Options”, find and select the “Values” parameter. Schematically, this item is displayed as a button “123”.
9. Changed values ​​will be moved to the appropriate cells. To remove unnecessary values ​​in the same menu, select the category “Clear contents”.

Thus, the replacement of commas for periods in the selected range of values ​​has been carried out, and unnecessary values ​​have been removed.

Method 3: Adjust Excel Options

By adjusting some parameters of the Excel program, you can also achieve the replacement of the symbol “,” with “.”. In this case, the format of the cells will remain numeric, and will not change to text.

1. By activating the “File” tab, select the “Options” block.
2. You should go to the “Advanced” category and find “Editing Options”. Uncheck the box next to the “Use system separators” criterion. In the line “Separator of integer and fractional parts” we change the dot, which is by default, to a comma.

After the changes made in the Excel program settings, the delimiter for denoting fractions is now a period.

Method 4: Use a Custom Macro

Another method of replacing semicolons in Excel involves the use of macros. But before using them, you should take into account the fact that macros are disabled by default in the program. So, to get started, you need to enable the “Developer” tab and activate macros.

The “Developer” mode is enabled through the program settings. In the subsection called “Customize the ribbon”, then in the category “Main tabs” we find the item “Developer”, in front of which we put a tick. The settings are activated after pressing the “OK” button.

1. Tab “Developer” → block “Code”, press the button called “Visual Basic”.
2. The macro editor window will open. In this window, you need to enter the following program code:

At this stage, we complete the work in the editor by simply closing the editor window.

3. Select the cells in which the changes will be made. Presses the “Macros” button, which is located in the toolbox.
4. A window appears showing the available macros. Select the newly created macro. With the macro selected, click “Run” to activate it.

5. The replacement is done – dots appeared instead of commas.

The application of this method requires special attention. After activating the macro, it is no longer possible to return everything back. When selecting cells with certain values, you need to make sure that changes will be made only to those data that really need it.

Method 5: Change the computer’s system settings

This method is not very common, however, it is also used to replace commas with periods when performing calculations in Excel documents. We will change the settings directly in the software. Consider this process using the example of Windows 10 Pro software.

1. We go to the “Control Panel”, which can be called through the “Start”.

2. In the “Time and language” category, select the “Region” option.
3. After that, a window will open. Here we activate “Additional options for date, time, region”.

4. A new window will open, in which we go to “Regional Standards”.

5. Now go to the “Formats” tab and at the bottom of the window activate “Advanced options …”.

6. Next, in the “Numbers” category, specify the required separator character in the line “Separator of the integer and fractional parts.” After making the changes, click “OK”.

As a result of our work, commas in cells-fields of Excel tables filled with numeric values ​​will be automatically transformed into periods. In this case, the cell format does not matter, whether it is “General” or “Numeric”.

Important! When transferring a file to another computer with standard settings, problems with the calculation process may occur.

Additional Method: Replacing a dot with a comma in Excel using Notepad

Windows software has a Notepad program that functions on the basis of a minimum number of functions and settings. “Notepad” can be used as an intermediary for copying, previewing data.

1. You need to select the desired range of cells and copy it. Open Notepad and paste the copied values ​​into the window that opens.

2. In the “Edit” tab, select the “Replace” category. As hot keys, the combination “CTRL + H” is used. A window appears in which we fill in the fields. In the line “What” enter “,”, in the line “What” – “.”. When the fields are filled, click “Replace All”.

After these manipulations in the inserted text, all commas were transformed into periods. Now it remains only to copy the changed fractional values ​​uXNUMXbuXNUMXbagain and paste them into the table of the Excel document.

Conclusion

The article examined the most effective and popular methods for replacing the comma character in decimal fractions with dots in Excel spreadsheets. Most often, users prefer the built-in Find and Replace tool for the visually appealing look of numerical values, and the SUBSTITUTE function is used to perform calculations.