Four ways in Excel to replace a semicolon

Professionally working at Excel, people often have to replace a comma (,) with a period (.). This is most often associated with editing English-language localization files, since abroad a single standard implies the use of a period as a decimal separator, while we have a comma. And vice versa, a comma is used to separate the discharge, and we have a period. In this article, we’ll talk about how to replace a comma with a special tool in Excel so that this process takes as little time as possible.

First way: Find and Replace

In the first method, we will use a tool called Find and Replace. But before you replace a comma with a point in Excel in this way, it’s worth saying that it is completely unsuitable in the case of formulas, since all the values ​​in the table will go into text format. So let's get started:

1. First you need to convert the cell format to text. To do this, select the desired area, click RMB and click on "Format Cells" in the menu.
2. In the window that appears, on the "Number" tab, select "Text" and click on "OK."
3. After changing the format of the cells, you can start replacing. To do this, on the main tab, click on the "Find and Select" button and select "Replace" in the drop-down list.
4. In the window that appears, in the "Find" field, enter a point, and in the "Replace with" - a comma.
5. Click the Replace All button.

This was the first way in Excel to replace the point with a semicolon. But perhaps it will not suit you, so we move on to the second.

Second way: formulas

Now we will figure out how to use a formula to replace a comma with a period in Excel. Its essence lies in the fact that with the help of a special operator we transform the data in other cells, and then transfer them to the required range:

1. Click on the cell that is located first next to those in which you want to make a replacement.
2. Click on the "Insert Function" button.
3. In the list we find the "Substitute" function.
4. Click on "OK."

Next, a function window will appear in which you need to enter all the necessary values:

1. In the "Text" enter the first cell in which you want to replace.
2. In "Star_text" enter a comma, taking it in quotation marks.
3. In the "New Text" enter the point, also taking it in quotation marks.
4. Click OK.

Now, holding the LMB in the lower right corner of our selected cell, drag it to the end of the desired range. The entire area was filled with data with replaced dots. Now you need to do the following:

1. Highlight changed values.
2. Press CTRL + X.
3. Select unchanged values.
4. Click RMB.
5. Select the insert option labeled "123".

So we have analyzed the second method, how in Excel to replace the point with a comma. We pass to the third.

Third way: macro

Now you need to consider how you can use VBA to replace the comma with a period in Excel. To do this, you need:

1. Go to the "Developer" tab.
2. Click on Visual Basic.
3. In the window that appears, insert the text that will be indicated below this list.
4. Close a window.

Now you can start the replacement.

1. Select the desired cells.
2. Go to the "Developer" tab.
3. Click the Macros button.
4. In the window that appears, highlight the newly created macro.
5. Click Run.

After that, the task, as in Excel to replace the point with a semicolon, can be considered solved. We pass to the last - fourth method.

Fourth method: settings

This method is suitable in cases where you need to make a replacement so that the formulas remain valid. It is performed as follows:

1. Click the "File" button.
2. Go to the Options menu.
3. In the window that appears, go to the "Advanced" section.
4. In the "Insert Options" menu, uncheck the box next to "Use system separators."
5. In the "Separator of integer and fractional parts", replace the comma with a period.
6. Click on "OK."

After the done actions, all formulas will be displayed normally by the program. And on this article comes to an end. We hope that one of the proposed methods has helped you achieve the desired results.