How in "Excel" to replace the point with a comma? Customize Excel

Getting used to the existing order of things, we do not think much about the fact that someone can live by other laws. In fact, this is not the dictum of the Dalai Lama, but a description of one of the office problems that can cause a regional setting of Excel. The essence of it is that in different countries, even using the same Arabic numerals, it is customary to write down the numbers somewhat differently. A vivid example of this difference, which usually leads to problems, is the sign separating the fractional part of the number from the integer. In Russia, and indeed in the former post-Soviet space, it is always a comma, but in the West - a point.

Point Value in Excel

Let's look at the undesirable consequences that can result from the presence in the system of an "incorrect" or, more accurately, an incorrect delimiter. In most cases, the number containing the point will be interpreted by Excel as a date, and, accordingly, instead of "25.11" you will receive in the cell "25.Noyab".

When working with a large amount of data in a table, you can immediately not notice such a conversion. Moreover, not every number can be so distorted. For example, "25.13" will remain unchanged. As you probably already guessed, the conversion will affect only those numbers where before or after the point there will be numbers from 1 to 12. This range is used to denote months of the year and, therefore, it is perceived as a date. If such number falls one or two by a hundred, then all this will manifest itself only at the stage of calculations.

We replace the point

There are several options for how to put commas in Excel. We will consider only the simplest and most practical that any user can use, regardless of the level of computer literacy.

• How in "Excel" to replace a point with a comma, using the parameters of the program itself? To do this, go to the "Options" section of the spreadsheet editor and go to the "Advanced" menu. In this section, you need to remove the check-box in the "Use system dividers" item. Unlocking the following two items in this way, you will be able to change the separation marks to the ones necessary to work with your data.

• How can I replace the point with a comma in Excel using the operating system parameters? In the Windows Control Panel, you will need to find the "Regional and Language Options" section. In the opened window on the "Format" tab we switch to the submenu "Additional parameters" and on the "Numbers" tab we change the separators of the whole and fractional part to the required ones.

• How in "Excel" to replace a point with a comma using the function "Search and replace"? This menu is probably familiar to most users and using it will not require additional comments. The only important thing to do is to prioritize the editable area. If you do not perform this operation, Excel will change all available points on the sheet and, having eliminated the discrepancy in one part of the table, you can get it in another.

Another old, but proven way is to pre-replace the delimiters in any simple text editor. Regular "Notepad" from the standard Windows programs can perfectly cope with this task. The mechanism of action is similar to the last described method and consists of simply replacing the characters needed by your table editor.

Differences in the menu of office packages

In the office package from Microsoft with each new version, the location of the menu items changes somewhat. Excel 2013 was not an exception. In addition to the design changes that touched on color schemes and the appearance of icons, the parameter "Parameters" shifted on the command tape. He moved from the bottom of the main drop down menu settings to its side.

In all other respects, the user's actions in Excel 2013, when using the tips from the previous section, are completely similar to working with earlier versions. Once you open the shifted side of the settings menu, you will be taken to the familiar interface.

If to summarize, then all versions of the office, with different colors for each program, contain the option parameters in the sidebar. These include the version of 2013 and all subsequent, more modern versions.

Underwater rocks

Now, understanding how in "Excel" to replace the point with a comma, we will understand the peculiarities of the methods described. Changing the parameters of the operating system or program settings, of course, can help in case of a one-time need, but it is not very comfortable to work in this mode all the time.

It is much more preferable from the point of view of practicality to see a one-time replacement of data in the required area using the search menu. In this case, you do not risk distorting your data in other tables and you can visually control the process of changing fractional separators.

Text format and calculations

In addition to everything described, there is another interesting feature of the Excel spreadsheet editor. The text entered by the method of copying, in some cases may not be recognized as digital information. The cell in which the digit was saved as text will be indicated by a green triangle in the upper left corner. Outwardly everything looks decent, the figures are not distorted, but when you try to make calculations, the data from such a cell will not be taken into account.

Thus, a correctly looking table will produce an incorrect final result if you perform calculations in it. This problem is solved by the regular means of Excel. By selecting a cell with a green angle, you can find a pop-up exclamation point in the yellow triangle next to it. Clicking on it will bring up a small context menu. By selecting the "Convert to number" option in the upper parameter, you change their representation without loss of data.

After such a simple operation, all calculations will be performed correctly. To speed up, you can select more than one such cell, and several adjacent ones and simultaneously change their parameters using the context menu.

Finally

In this material, we resulted in fast and really working ways of correcting the representation of the kind of digital data in the Excel program. Applying them in practice will help keep your nerves and time working in spreadsheets.