Replacing commas with dots in Excel: 5 methods

To separate the integer and fractional parts of a number represented as a decimal fraction, a special separator character is used: in English-speaking countries it is a dot, in the rest it is most often a comma. Because of this difference, Excel users are often faced with the task of replacing certain characters with the ones they need. Let’s see how you can change commas to dots in the program.

Note: if a comma is used as a separator, then the program will not accept numbers with dots as decimal fractions, which means that they cannot be used in calculations either. This is also true for the reverse situation.

Method 1: Use the Find and Replace Tool

This method is the most popular and involves the use of a tool “Find and Replace”:

1. In any convenient way, we select a range of cells in which all commas must be replaced with dots. In the main input in the block “Editing” click on the function icon “Find and select” and in the proposed options we stop at the option – “Replace”. You can also use the keyboard shortcut to launch this tool. Ctrl + H.Note: if you do not make a selection before using the tool, then the search and replacement of commas with periods will be performed throughout the contents of the sheet, which is not always necessary.
2. A small function window will appear on the screen. “Find and Replace”. We should immediately be in the tab “Replace” (if for some reason this did not happen, we switch to it manually). Here we are in the parameter value “Find” specify a comma sign for “Replaced by” – dot sign. Press the button when ready “Replace All”to apply the tool to all selected cells.Pressing the same button “Replace” will perform a single search and replace, starting from the first cell of the selected range, i.e. it will need to be clicked exactly as many times as there are replacements according to the given parameters.
3. The next window will contain information about the number of replacements performed.
4. Thus, without much effort, we managed to insert dots instead of commas in the selected fragment of the table.

Method 2: use the “Substitute” function

With this function, you can also automatically search for and replace one character with another. Here’s what we do:

1. We get up in an empty cell next to the one that contains a comma (in the same line, but not necessarily in the next one). Then click on the icon “Insert function” on the left side of the formula bar.
2. In the opened window Feature inserts click on the current category and select “Text” (also suitable “Full alphabetical list”). In the proposed list, mark the operator “SUBSTITUTE”, then press OK.
3. A window will appear in which you need to fill in the function arguments:
   • “Text”: Specify a reference to the original cell containing a comma. You can do this manually by typing the address using the keyboard. Or, being in the field for entering information, click on the desired element in the table itself.
   • “Star_Text”: here, as with the function “Find and Replace”, indicate the sign to be changed, i.e. comma (but this time in quotation marks).
   • “New_text”: specify the dot sign (in quotation marks).
   • “Entry_number” is not a required argument. In this case, leave the field empty.
   • You can switch between function arguments by simply clicking inside the desired field or using the key Tab on keyboard. When everything is ready, click OK.
4. We get the processed data in the cell with the operator. To get a similar result for other elements of the column, use fill marker. To do this, hover over the lower right corner of the cell with the function. As soon as the pointer changes to a black plus sign (this is marker), hold down the left mouse button and drag it down to the last element of the column.
5. By releasing the mouse button, we will immediately see the result. It remains only to move the new data into the table, replacing the original ones with them. To do this, select the cells with formulas (if the selection was suddenly removed), right-click on the marked area and select the item in the context menu that opens. “Copy”.You can also use a similar button located in the toolbox “Clipboard” in the main tab of the program. Or just press hotkeys Ctrl + C.
6. Now we select a range of cells in the table itself, where we should paste the data copied to the clipboard. Right-click on the selected area, in the menu that opens in “Paste Options” select the icon with the image of the folder and the numbers 123, – command “Insert Values”.Note: Instead of selecting a range in the source table, you can simply move to the topmost cell (or the top-leftmost cell, if we are talking about an area of ​​​​multiple columns and rows), starting from where you want to paste the copied data.
7. All commas in the column have been replaced with periods. We no longer need the auxiliary column, and we can remove it. To do this, click on its designation on the horizontal coordinate bar with the right mouse button and in the context menu that opens, stop on the command “Delete”. When performing the operation, you need to make sure that there is no valuable data in the rows below this column, which will also be deleted.An alternative way is to clear the contents of the cells. To do this, select them, call the context menu by right-clicking on them and select the appropriate command in the list that opens.

Method 3: Adjust Excel Options

Let’s move on to the next method, which differs from those discussed above in that we will perform actions not in the program’s working environment (on a sheet), but in its settings.

It should be noted that, in which you want to perform a replacement, must be selected as Numerical (or General) so that the program perceives their contents as numbers and applies the specified settings to them. So let’s get started:

1. Go to the menu “file”.
2. Select an item from the list on the left “Parameters”.
3. In subsection “Additional” uncheck the option “Use system separators” (parameter group “Edit Options”), after which the field opposite is activated “Integer and fraction separator”, in which we indicate the sign “point” and click OK.
4. Thus, commas will be replaced by dots in all cells containing numeric values. The action will be performed in the entire workbook, not just on this sheet.

Method 4: Use a Custom Macro

This method cannot be called popular, however, it exists, so we will describe it.

To begin with, we need to perform preliminary preparation, namely, enable the mode Developer (off by default). To do this, in the program parameters in the subsection “Customize Ribbon” in the right part of the window, check the box next to the item “Developer”. Confirm changes by pressing the button OK.

Now let’s get down to our main task:

1. Switching to the tab that appears “Developer” click on the icon on the left side of the ribbon “Visual Basic” (tool group “the code”).
2. A window will appear on the screen. Microsoft VB editor. On the left side, double-click on any sheet or book. In the field that opens, paste the code below and close the editor.

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

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

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

   ReplaceFormat:=False

   End Sub

3. We select the cells in the contents of which you want to make a replacement. Then click on the icon “Macro”.
4. In the window that appears, mark our macro and confirm the execution of the command by pressing the appropriate button. Please note that this action cannot be undone.
5. As a result, all commas in the selected cells will be replaced with dots.

Note: this method works only if a point is used as a decimal separator in the program, i.e. option “Use system separators” (discussed above) is disabled.

Method 5: Change the computer’s system settings

Let’s finish in a way that involves making changes to the settings of the operating system itself (let’s look at the example of Windows 10).

1. Run Control panel (for example, through the line Search).
2. In view mode “Small/large icons” click on applet “Regional Standards”.
3. In the window that opens, we will find ourselves in the tab “Format”in which we press the button “Extra options”.
4. In the next window in the tab “Numbers” we can specify the delimiter character that we want to set as the default for the system and the Excel program in particular. In our case, this is a point. Press when ready OK.
5. After that, all commas in table cells that contain numeric data (with the format – Numerical or General) will be replaced by dots.

Conclusion

Thus, there are several ways in Excel that you can use to replace commas with periods in table cells. Most often, this is the use of the Find and Replace tool, as well as the SUBSTITUTE function. Other methods are needed in exceptional cases and are used much less frequently.