4 methods to change comma to decimal point in Excel

The use of a decimal point instead of a comma can lead to significant consequences in Excel calculations. These mistakes most frequently occur when data are imported into the spreadsheet from other sources.

If a decimal point is used instead of a comma in fractions, the program automatically perceives them as a text data type. Therefore, before doing mathematical calculations and computations you should format and prepare imported data.

How to change decimal point to comma in Excel?

Highlight and copy data from the spreadsheet given below:

№	Currency pair	Conversion	Sale	Purchase	Spread
1	EUR/USD	1	1,1347	1,1350	0,0003
2	GBP/USD	1	1,5438	1,5442	0,0004
3	USD/CHF	1	0,9543	0,9547	0,0004
4	USD/JPY	100	1,1948	1,1945	-0,0003

Now go to the worksheet and right-click cell A1. Select «Paste Special» option in the appeared shortcut menu. In the dialog box select «Unicode Text» and click OK.

As you can see, Excel recognizes only those numbers, which are located in column C. Values in this column are right aligned. In other columns values are left aligned. In all cells the format is «General» by default.

Note. If you copy data from other sources without Paste Special, format is copied together with other data. In this case «General» format of cells (by default) can be changed, making it impossible to distinguish visually whether the number or the text has been recognized.

All further actions should be performed on a blank worksheet. Delete everything from the sheet and open a new one for further work.

To convert decimal point to comma in imported data you can use 4 methods:

Method 1: converting decimal point to comma in Excel through Notepad

Windows Notepad doesn’t require applying complicated settings or functions. It is merely an intermediary in copying and preparing data.

Copy the data from the original spreadsheet on this page. Open Windows Notepad ("Start" - "All programs" - "Standard" - "Notepad") and paste the copied data into it for preparation.

Select «Replace» option in «Edit» menu (or use a combination of hot keys CTRL + H). In the appeared dialog box, type decimal point (.) in «What» field and comma (,) in «With» field. Then click «Replace All» button.

Notepad program replaced all decimal points with commas. Now the data is ready for copying and pasting in the worksheet.

This is a rather simple, but very effective way.

Method 2: temporary change of Excel settings

Before you change a decimal point to a comma in Excel, correctly evaluate the set task. Perhaps it is better to make the program temporarily perceive a point, as a decimal separator in fractional numbers. In the settings you need to specify that in fractional numbers the separator is a decimal point instead of a comma.

To do this, open «FILE»-«Options»-«Advanced». You should temporarily uncheck «Used system separators» in «Editing options» section. Then remove a comma and enter a decimal point in «Decimal separator и Thousands separator» field.

After performing the calculations, you are strongly recommended to return the default settings.

Attention! This method works if you make all the changes before importing data, not after it.

Method 3: temporary change of Windows system settings

The principle of this method is similar to the previous one. But here you change the similar settings in Windows. You need to replace the comma with a decimal point in the settings of the regional operating system standards. Now, more details on how to do this.

Open "Start" - "Control Panel" - "Languages and Regional Standards". Click on «Advanced options» button. In the appeared window, make change in the first field «Delimiter of integer and fractional part» and enter the value you need. Then click OK and OK.

Attention! If you open this file on another computer where other system parameters of regional standards are set, you may experience computational problems.

Method 4: using find and replace function in Excel

This method is similar to the first one. Only here we use the same function from Notepad, but this time in Excel itself.

Applying this method, in contrast to the above mentioned ones, first insert the copied spreadsheet on a blank worksheet, and then prepare it for computations and calculations.

An important drawback of this method is the complexity of its implementation, if some fractional numbers with a decimal point after the insertion have been recognized as a date, and not as a text. Therefore, first get rid of the dates, and then deal with the text and the decimal points.

Pre-select the columns where the fractional numbers with a decimal point as a separator will be located. In this case, these are three D: F columns.
Set the text format of cells for the selected range to avoid the automatic conversion of some numbers to the date format. To do this, select the text format from the drop-down list on «HOME» tab in «Number» section. Alternatively, press CTRL + 1, select «Number» tab in the appeared «Format Cells» window, and select «Text» in «Category:» section.

Copy the spreadsheet and right click on cell A1. Select «Paste Special» option in the shortcut menu. Select «Unicode text» and click OK. Note how the values in cells D2, D3, D5, E3, E2, E5, are now displayed in contrast to the very first spreadsheet copying.

Click «HOME»-«Find and Select»-«Replace» tool (or press CTRL + H).

In the window that appears, enter a decimal point in «Find what:» field, and a comma in the second «Replace width:» field. Then click «Replace All».
Select 3 columns D: F again and change the format of the cells to «Number» (or press hot keys CTRL+SHIFT+1). Don’t forget to increase decimal as described in point 4.

All decimal points have been replaced with commas. The text has automatically converted to a number.

Instead of the 4th and 5th items, you can use in a separate column a formula with the following functions:

For example, select the range of cells G2: I5, enter this formula and press CTRL + Enter. Then move the cell values of G2: I5 range to D2: F5 range.

This formula finds a decimal point in the text with the help of =FIND() function. Then the second function changes it to a comma. And =VALUE() function converts the obtained result to a number.