For decimal numbers, Excel has a dot
. separator by default. The program uses the same number format as your PC’s Settings.
However, when you have large numbers in hundred thousands or millions format, you may want those numbers to be separated by a comma
, instead. It would increase the readability of such numbers.
If you want to change a dot to a comma, check out these 5 simple ways.
Use Comma Style
Firstly, you can apply a Comma format to your Numbers from the Excel Ribbon. This method is easy to use and very quick.
Select the numbers. From the Home tab, click on the Comma button.
Once you have the commas in your numbers, click on the Decrease Decimal button to hide the dot.
Alternatively, you can also do this from the Format Cells.
- Select Number cells and press the
Ctrl + 1shortcut key for Format cells.
- On the Category, choose Number. Tick the option for Use 1000 Separator (,).
- On Decimal places, decrease the Number and hit OK.
Use Find and Replace
Do you have a large dataset of numbers with dots?
In that case, the Find and Replace tool would be the best to convert all dots to a comma quickly.
Here, we will look for all the existing dots
. in the number and substitute it with the comma
- Select the Ranges with the Number.
Ctrl + Hkeyboard shortcut to bring up the Find and Replace menu.
- On Find What, enter a Dot
- On Replace with, type Comma
,. Click Replace All.
Use SUBSTITUTE Function
As the function name implies, the SUBSTITUTE function replaces one value with another.
Its functionality is similar to the Find and Replace command.
|=SUBSTITUTE(text, old_text, new_text, [instance_num])
|text: cell or value to replace
old_text: text or character you want to substitute.
new_text: character or text string you wish to replace your old_text with.
[Instance_num]: Enter the position of old_text to replace only that item.
If you skip this, the SUBSTITUTE function replaces all old_text occurrences.
You can use this method when you have multiple dots in a number, but convert only the specific one.
The SUBSTITUTE function allows you to choose a position of a dot to replace in the formula. Make sure you do not miss the Instnace_num argument.
For example, there’s a 32.13,8.8 number in cell E4. Let’s convert it.
=SUBSTITUTE(E4, ".", ",", 1)
In the formula, we have specified to substitute the dot with a comma in the E4 cell value after the 1st occurrence. So, our outcome is 32,13,8.8 where one dot is still intact.
From Excel Options
If you do not want to have the decimal number permanently, you can modify the menu from Excel Options.
Here, you can specify the Comma
, as a default Decimal Separator for all numbers. This setting will apply to all new workbooks you open.
Therefore, once you set the menu, you don’t have to keep converting the dot to the comma.
- On the Excel worksheet, click File tab > Options.
- Select Advanced. On Editing options, untick the Use system separators menu.
- On the Decimal separator, type
,Comma and click OK.
Change System Settings
By default, Excel uses your PC’s decimal separator format for numbers. So, you can also set a comma
, as a decimal separator on your PC’s Region settings.
- Head to Windows Search. Type
intl.cpland press Enter.
- On the Region tab, click on Additional Settings.
- On the Numbers Tab, set the Decimal symbol to
,. Then, click OK.
- Hit OK again.