Countries such as the USA and the UK use a comma to separate groups of thousands and a decimal point to separate decimals.
Many other countries such as Spain and Denmark use a decimal point to separate groups of thousands and a comma to separate decimals.
Therefore, sometimes you may need to change commas to decimal points in Excel to display numbers in a format that suits your audience.
In this tutorial, I will show you five techniques for changing commas to decimal points in Excel. It also shows how to change decimal points to commas in Excel.
Method #1: Temporarily Replace System Separators with Custom Separators in Excel
In this method, we use the Excel Options dialog box to change the options relating to the thousands and decimal separators.
In our illustration, we use the following example dataset that has commas separating groups of thousands and decimal points separating decimals.
Our goal is to change the commas to decimals and the decimals to commas.
We use the following steps:
- Select File to open the Backstage screen.
- Select Options on the left sidebar of the Backstage screen.
- Select Advanced on the left sidebar of the Excel Options dialog box.
- In the Editing Options section, uncheck the Use system separators option and replace what is in the Decimal separator text box with a comma (,) and what is in the Thousands separator text box with a decimal point (.).
- Click OK.
In our example dataset, the commas are replaced with decimal points and the decimal points are replaced with commas.
Note: If you want Excel to revert to using system separators, open the Excel Options dialog box and check the Use system separators checkbox.
Method #2: Use the Replace Feature of Windows Notepad
In this method, we first copy the data from Excel and paste it into Notepad.
We then make the desired changes in Notepad and then copy the data and paste it back into Excel.
In our illustration, we use the following example dataset that has commas separating groups of thousands and decimal points separating decimals.
Our objective is to change the commas to decimals and the decimals to commas.
We use the following steps:
- Select the dataset and press Ctrl + C to copy it.
- From the Windows search box, search and open the Notepad App.
- Press Ctrl + V to paste the dataset in Notepad.
- Open Notepad’s Replace dialog box using any of the following ways:
Press Ctrl + H
Or
Select Replace on the Edit menu.
- In the Replace dialog box, type a decimal point in the Find what text box and asterisk (*) in the Repace with text box, then click the Replace All button.
Note: The stars are temporary placeholders that shall be replaced with commas.
- In the Replace dialog box that is still open, replace what is in the Find what text box with a comma and what is in the Replace with text field with a decimal point, then click the Replace All button.
The commas are replaced with decimal points.
It is now time to replace the star placeholders with commas.
- In the Replace dialog box, replace what is in the Find what text box with asterisk (*) and what is in the Replace with text box with a comma, then click the Replace All button.
Dismiss the Replace dialog box by clicking the Cancel button.
The stars are replaced with commas. We now have the currency values in the format we want.
- Press Ctrl + A to select the whole dataset and press Ctrl + C to copy it.
- Open a new worksheet and press Ctrl + V to paste the dataset.
- Increase the column widths and bold the column headers to make the data more presentable.
Note: The values in column C are left-aligned because they are text strings.
Also read: Apply Currency Format in Excel (Shortcut)
Method #3: Change the Windows Regional Settings
In this method, we change the Windows regional settings to change the system separators used by Excel.
In our illustration, we use the following example dataset that has commas separating groups of thousands and decimal points separating decimals.
Our objective is to change the commas to decimals and the decimals to commas.
We use the steps below:
- From the Windows Search box, search and open the Contol Panel App.
- In the Control Panel window, select Change date, time, or number formats under the Clock and Region option.
- Click the Additional settings button in the Formats tab of the Region dialog box that appears.
- In the Numbers tab of the Customize Format dialog box, open the Decimal symbol drop-down list and select the comma symbol. Then open the Digit grouping symbol drop-down list and select the decimal point symbol.
- Click Apply and OK.
- Click Apply and OK in the Region dialog box.
Open your Excel dataset and see that commas have been changed to decimal points and decimal points have been changed to commas.
Note: The decimal point symbol now becomes your computer’s default separator for thousands and the comma symbol is the separator for decimals. Not only for Excel but all other applications.
If you want to restore your system’s original default separators navigate to the Customize Format dialog box as explained earlier in this tutorial and click the Reset button in the Numbers tab.
Select Yes in the message box that pops up.
Click OK in the Customize Format dialog box. Click Apply and OK in the Region dialog box.
Your system’s default settings for numbers are restored.
Also read: Show Thousands as K in Excel
Method #4: Use the Combination of SUBSTITUTE and FIXED Functions
In this method, we use the SUBSTITUTE and FIXED functions to change commas to decimal points and decimal points to commas.
The SUBSTITUTE function substitutes a new text string for existing text in a text string.
The FIXED function rounds a number to the specified number of decimals and returns the result as text with or without commas.
In our illustration, we use the following example dataset that has commas separating groups of thousands and decimal points separating decimals.
We use the following steps:
- Enter the following formula in cell D2:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FIXED(C2,2,FALSE),".","$"),",","."),"$",",")
- Double-click or drag down the fill handle to copy the formula down the column.
Explanation of the formula
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FIXED(C2,2,FALSE),”.”,”$”),”,”,”.”),”$”,”,”)
- SUBSTITUTE(FIXED(C2,2,FALSE),”.”,”$”) – The innermost SUBSTITUTE function replaces the decimal points with dollar ($) signs.
- SUBSTITUTE(SUBSTITUTE(FIXED(C2,2,FALSE),”.”,”$”),”,”,”.”) The second innermost SUBSTITUTE function replaces the commas with decimal points.
- =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FIXED(C2,2,FALSE),”.”,”$”),”,”,”.”),”$”,”,”) – The outermost SUBSTITUTE function replaces the dollar symbol ($) with a comma, completing the process of changing commas to decimal points and decimal points to commas.
Method #5: Use Excel Flash Fill Feature
In this method, we use the Excel Flash Fill Feature to change the commas in a dataset to decimal points and vice versa. The Flash Fill feature automatically fills in data once it detects a pattern.
We use the following dataset in our illustration. Our objective is to change the comma separator to a decimal point and the decimal point separator to a comma using the Flash Fill feature.
We use the following steps:
- Select cell D2 and enter the value 1.000,15. Select cell D3 and enter the value 2.000,23 as shown below:
Notice that Excel senses a pattern and suggests the next values in grey.
- Press Enter to accept the suggested values if they are correct.
Note: If the Flash Fill feature does not show suggested values, select cell D4 after you have entered the two values then do either of the following:
Press Ctrl + E
Or
In the Data tab, select Flash Fill in the Data Tools group.
The Flash Fill feature detects a pattern in the first two values you entered and fills in the rest of the values.
In this tutorial, we have looked at 5 techniques for changing commas to decimal points and decimal points to commas in a dataset.
You may need to do this to display numbers in a format that suits certain audiences.
Other articles you may also like:
