Replacing dots with commas in Excel with different methods

Quite often, while working in Excel tables, it becomes necessary to replace dots with commas. This is often due to the fact that in English-speaking countries a dot is used to separate the fractional and integer parts in a number, while in our country a comma serves for this purpose.

And everything would be fine, but the problem is that in the Russified version of Excel, data with a dot is not perceived as numbers, which makes it impossible to further use them in calculations. And to fix this, you need to replace the dot with a comma. How exactly this can be done in Excel, we will consider in this article.

Method 1: Using the Find and Replace Tool

We will start with, perhaps, the simplest method, which involves the use of a tool “Find and Replace”, when working with which you need to be extremely careful not to accidentally replace periods with commas in data where this should not be done (for example, in dates). So here’s how it works:

1. Go to the tab “Home”, and click on the button “Find and select” (magnifying glass icon) in the block “Editing”. A list will open where we select a command “Replace”. Or you can just press the key combination Ctrl + H.
2. A window will appear on the screen. “Find and Replace”:
   • in the field for entering a value opposite the item “Find” we write a symbol “.” (point);
   • in the “Replace with” field, write the sign “,” (comma);
   • press the button “Parameters”.
3. More options will appear for you to perform Find and Replace. Clicking on the button “Format” for parameter “Replaced by”.
4. In the window that appears, specify the format of the corrected cell (the one that we get in the end). According to our task, we choose “Numerical” format, then click OK. If desired, you can set the number of decimal places, as well as separate groups of digits by setting the appropriate checkbox.
5. As a result, we will again find ourselves in the window “Find and Replace”. Here we definitely need to select the area of ​​cells in which the points will be searched and then replaced with commas. Otherwise, the replace operation will be performed on the entire sheet, and data that should not have been changed may be affected. Selecting a range of cells is done with the left mouse button pressed. Press when ready “Replace All”.
6. All is ready. The operation was successfully completed, as evidenced by the information window with the number of replacements performed.
7. We close all windows (with the exception of Excel itself), after which we can continue working with the converted data in the table.

Note: in order not to select a range of cells when setting parameters in the window “Find and Replace”, you can do it in advance, i.e. first select the cells, and then launch the appropriate tool through the buttons on the program ribbon or using a keyboard shortcut Ctrl + H.

Method 2: SUBSTITUTE function

Let’s now look at the function “SUBSTITUTE”, which also allows you to replace dots with commas. But unlike the method that we discussed above, the replacement of values ​​​​is not performed in the initial ones, but is displayed in separate cells.

1. We go to the topmost cell of the column where we plan to display data, after which we press the button “Insert function” (fx) to the left of the formula bar.
2. In the opened window Function Wizards choose a category – “Text”, in which we find the operator “SUBSTITUTE”, select it and click OK.
3. We will find ourselves in a window with function arguments that need to be filled in:
   • in the value of the argument “Text” specify the coordinates of the first cell of the column in which you want to replace dots with commas. You can do this manually by entering the address using the keys on the keyboard. Or you can first click the mouse inside the field for entering information, and then click on the desired cell in the table.
   • in the value of the argument “Star_Text” we write a symbol “.” (point).
   • for argument “New_text” specify a symbol as a value “,” (comma).
   • value for argument “Entry_number” may not be filled.
   • click when ready OK.
4. We get the desired result in the selected cell.
5. It remains only to extend this function to the remaining rows of the column. Of course, you don’t need to do this manually, since Excel has a handy autocomplete function. To do this, move the cursor to the lower right corner of the cell with the formula, when the pointer changes to a black plus sign (fill marker), hold down the left mouse button and drag it down to the very last line involved in the data conversion.
6. It remains only to move the converted data to the place in the table where it should be. To do this, select the cells of the column with the results (if the selection is cleared after the previous action), right-click on any place in the selected range and select the item “Copy” (or press the keyboard shortcut Ctrl + C).
7. Then we select a similar range of cells in the original column whose data has been converted. We right-click on the selected area and in the context menu that opens, in the paste options, select “Values”.
8. After pasting the copied data, an exclamation mark icon will appear next to it. Click on it and select from the list “Convert to number”.
9. Everything is ready, we got a column in which all periods are replaced by commas.
10. The work column used to work with the function SUBSTITUTE, is no longer needed and can be removed via the context menu. To do this, right-click on the column designation on the horizontal coordinate bar and select the command from the list that appears. “Delete”.
11. The above actions, if required, can be performed in relation to other columns of the source table.

Method 3: Using a Macro

Macros also allow you to replace a dot with a comma. Here’s how it’s done:

1. First you need to make sure that the tab is enabled “Developer”which is disabled by default in Excel. To enable the desired tab, go to the menu “File”.
2. In the list on the left, go to the section “Parameters”.
3. In the program options, click on the section “Customize Ribbon”, after which, in the right part of the window, put a tick in front of the item “Developer” and click OK.
4. Switch to tab “Developer”in which we click on the button “VisualBasic”.
5. In the editor, click on the sheet on which we want to make a replacement, in the window that opens, paste the code below, and then close the editor:

   Sub Макрос_замены_точки_на_запятую()

   Selection.Replace What:=".", Replacement:=".", LookAt:=xlPart, _

   SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

   ReplaceFormat:=False

   End Sub

6. Now select the range of cells on the sheet where we plan to perform the replacement, and then click on the button “Macro” all in the same tab “Developer”.
7. A window will open with a list of macros, in which we select “Macro_replacing_dot_by_comma” and push “Run”.
8. As a result, we will get cells with converted data, in which dots have been replaced with commas, which is what we needed.

Method 4: Using Notepad

This method is implemented by copying data into the editor built into the Windows operating system. Notebook for later editing. The procedure is shown below:

1. To begin with, we select a range of cells in the values ​​of which we need to replace dots with commas (let’s consider one column as an example). After that, right-click on any place in the selected area and select the command from the menu that opens. “Copy” (or you can use the keyboard shortcut Ctrl + C).
2. Run Notebook and paste the copied information. To do this, right-click and select the command from the drop-down menu. “Insert” (or use a combination Ctrl + V).
3. On the top menu bar, click on “Edit”. A list will open, in which we click on the command “Replace” (or press hotkeys Ctrl + H).
4. A small replacement window will appear on the screen:
   • in the field for entering the parameter value “What” print character “.” (point);
   • as a value for a parameter “How” put a symbol “,” (comma);
   • push “Replace All”.
5. Close the replacement window. Select the converted data, then right-click on it and select the command “Copy” in the context menu that opens (you can also use Ctrl + C).
6. Let’s go back to Excel. We mark the area where you want to insert the changed data. Then right-click on the selected range and select the command “Keep text only” in the insert options (or click Ctrl + V).
7. It remains only to set the cell format as “Numerical”. You can select it in the toolbox “Number” (tab “Home”) by clicking on the current format and selecting the desired one.
8. The task was successfully completed.

Method 5: Setting Excel Options

By implementing this method, we need to change certain program settings.

1. Go to the menu “file”, where we click on the section “Parameters”.
2. In the program parameters in the list on the left, click on the section “Additional”… In the settings block “Edit Options” remove the checkbox next to the options “Use system separators”. After that, the fields for entering characters as separators are activated. As a separator of the integer and fractional parts, we write the symbol “.” (dot) and save the settings by pressing the button OK.
3. There will be no visual changes in the table. Therefore, we move on. To do this, copy the data and paste it into Notebook (let’s look at the example of one column).
4. Extracting data from Notepad and insert back into the table Excel in the same place where they were copied from. The alignment of the data has changed from left to right. This means that now the program perceives these values ​​as numeric.
5. Go back to the program settings (section “Additional”), where we return the checkbox opposite the item “Use system separators” in place and press the button OK.
6. As you can see, the dots were automatically replaced by the program with commas. Don’t forget to change the data format to “Numerical” and you can work with them further.

Method 6: System Settings

And finally, consider another method that is similar to the one described above, but involves changing the settings not of Excel, but of the Windows operating system.

1. We go in Control panel in any convenient way. For example, this can be done via Searchby typing the desired name and selecting the found option.
2. Set the view as small or large icons, then click on the section “Regional Standards”.
3. The region settings window will appear, in which, being in the tab “Format” click on the button “Additional settings”.
4. In the next window with the format settings, we see the parameter “Integer/Decimal Separator” and the value set for it. Instead of a comma, write a period and press OK.
5. Similarly to the fifth method discussed above, we copy data from Excel to Notebook and back.
6. We return the format settings to their original position. This action is critical, as otherwise errors may occur in the operation of other programs and utilities.
7. All dots in the column we were working on were automatically replaced with commas.

Conclusion

Thus, Excel provides 5 different methods, using which you can replace dots with commas, if such a need arises during work. In addition, you can use another method, which involves making changes to the settings of the Windows operating system itself, in which Excel is installed.