## Change Separators from Commas to Decimals or Decimals to Commas in Microsoft Excel

by Avantix Learning Team | Updated November 23, 2021

Applies to: Microsoft^{®} Excel^{®}2013, 2016, 2019 and 365 (Windows)

Depending on your country or region, Excel may display decimal points or dots instead of commas for larger numbers. The decimal point (.) or comma (,) is used as the group separator in different regions in the world. You can change commas to decimal points or dots or vice versa in your Excel workbook temporarily or permanently. The default display of commas or decimal points is based on your global system settings (Regional Settings in the Control Panel) and Excel Options.

If you want to use a decimal point instead of a comma as a group or thousands separator, which is different from your Regional Settings, this can lead to issues with your Excel calculations.

Excel recognizes numbers without group or thousands separators so these separators are actually a format.

Your Regional Settings in your Control Panel on your device are used by default by Excel to determine if decimals should be a period or dot (used in the US and in English Canada for example) or if decimals are a comma (used in many European countries and French Canada).

**Recommended article:** 10 Great Excel Pivot Table Shortcuts

*Do you want to learn more about Excel?*Check out our virtual classroom or live classroomExcel courses >

### Changing commas to decimals by changing global system settings

It's important to enter data in Excel in accordance with your global system settings in the Control Panel. For example, if you enter the number 1045.35 and your system is set with US as the region, the period is used as the decimal separator. If you enter the number 1045,35 in France, the comma is used as the decimal separator based on system settings with France as the region.

Below is the Control Panel in Windows 10:

To change the Regional Settings in the Control Panel in Windows 10:

- In the Start a search box on the bottom left of the screen, type Control Panel. The Control Panel appears.
- Click Clock and Region. This may appear as Languages and Regional Standards.
- Below Region, click Change Date, Time, or Number Formats. A dialog box appears. If you change the region, Excel (and other programs) will use settings based on the selected region so US would use periods as decimals and commas as thousands separators.
- If you don't want to change the region, click Additional settings. A Customize Format dialog box appears.
- Click the Numbers tab.
- Select the desired options in Decimal symbol and Digit grouping symbol. You can also change other options in this dialog box (such as the format of negatives).
- Click OK twice.

Below is the Customize Format dialog box:

Keep in mind that this will change defaults on your device for all Excel workbooks (as well as other programs like Microsoft Access or Microsoft Project). If another user opens the workbook on a different device, the separators will display based on their system settings.

### Changing commas to decimals and vice versa by changing Excel Options

You can also change Excel Options so that commas display as the group or thousands separator instead of decimals and vice versa. This option will affect any workbook you open after you have made this change.

To change Excel Options so that commas display as the thousands separator and decimals appear as the decimal separator (assuming your global settings in Regional Settings are set in the opposite way):

- Click the File tab in the Ribbon.
- Click Options.
- In the categories on the left, click Advanced.
- Uncheck Use system separators in the Editing area.
- In the Decimal separator box, enter the desired character such as a decimal or period (.).
- In the Thousands separator box, enter the desired character such as a comma (,).
- Click OK.

Below is the Options dialog box where Use system separators has been turned off and a decimal and comma have been entered as separators:

You may want to change the Options back to Use system separators when you have finished your task.

### Creating a formula to change separators and format using current system separators

Another alternative is to write a formula in another cell or column to convert separators. This works well if the data is text with separators that are not used in the current system settings. If you write a formula to convert text to numbers, you can keep the original values as well. In Excel 2013 or later, you can use the NUMBERVALUE function to convert the text to numbers.

The NUMBERVALUE function has the following arguments:

**Text** – The text to convert to a number.

**Decimal_separator** – The character used in the original cell to separate the integer and fractional part of the result.

**Group_separator** – The character used in the original cell to separate groupings of numbers, such as thousands from hundreds and millions from thousands.

The function is entered as follows:

**=NUMBERVALUE(Text,Decimal_separator, Group_separator)**

For example, if you have 2.500.300,00 in A1 and your device is set to US in the Regional Settings, you could enter the following formula in B1:

**=NUMBERVALUE(A1,",",".")**

The value that is returned will normally appear as a number without formatting. You can then apply appropriate formatting by pressing Ctrl + 1 and using Format Cells to apply formatting with system separators.

### Changing separators using Flash Fill

Flash Fill is a utility that is available in Excel 2013 and later versions and can be used to clean up and / or reformat data. It is not a formula, so if the original data changes, you'll need to rerun Flash Fill.

In the example below, the numbers entered in column A are formatted using system settings (US in this case). In column B, we have entered sample data in B2 and B3 with different separators (periods for the group separator and commas for the decimal separator). It's usually best to enter a few examples to show Excel the pattern. You'll need data with a pattern to the left in order to use Flash Fill. Once you have entered a few examples of the pattern in cells to the right of the original data, in the cell below the samples (in this case in cell B4) press Ctrl + E. Flash Fill will fill in the remaining cells until it reaches the end of the data set.

The following example was created using Flash Fill in column B (the data that is filled in column B is text):

For more information, check out How to Use Flash Fill in Excel (4 Ways with Shortcuts).

### Changing commas to decimals in an Excel worksheet for a selected range using Replace

You can use the Replace command to replace commas with decimals or vice versa In a range of cells on a worksheet. It's important to understand that this will change the range of cells to text so you won't be able to use certain functions like SUM. You may want to replace on a copy of the data.

To change commas to decimals using Replace:

- Select the range of cells in which you want to replace commas with decimals. You may select a range of cells, a column or columns or the entire worksheet.
- Press Ctrl + 1 or right-click and select Format Cells. A dialog box appears.
- Click the Number tab.
- In the categories on the left, click Text and then click OK. Notably, the data will be text not numbers.
- Click the Home tab in the Ribbon and in the Editing group, click Find & Select. A drop-down menu appears.
- Click Replace. Alternatively, press Ctrl + H. A dialog box appears.
- In the Find and Replace dialog box, click the Replace tab.
- In the Find what box, enter a comma (the character that you want to find).
- In the Replace with box, enter a decimal or period (the character that you want to replace).
- Click Replace All if you are sure that you want to replace all symbols or characters in the selected range. Click Replace if you want to find and replace one by one in the selected range. Click Find All if you want to find all of the characters to be replaced first and then decide if you want to replace all. Click Find Next if you want to find characters one by one and decide if you want to replace each character by clicking Replace. After replacing all characters, Excel displays a dialog box with the number of replacements.

Below is the Find and Replace dialog box with a comma (,) entered in the Find what box and a decimal (.) in the Replace with box:

In the previous example, only commas were changed to decimals but you may have data that has both commas and decimals (like 1,475.55). In this case, you would need to Replace multiple times.

To change commas to decimals and decimals to commas using Replace:

- Select the range of cells in which you want to replace commas with decimals and decimals with commas. You may select a range of cells, a column or columns or the entire worksheet.
- Press Ctrl + 1 or right-click and select Format Cells. A dialog box appears.
- Click the Number tab.
- In the categories on the left, click Text and then click OK. Notably, the data will be text not numbers.
- Click the Home tab in the Ribbon and in the Editing group, click Find & Select. A drop-down menu appears.
- Click Replace. Alternatively, press Ctrl + H. A dialog box appears.
- In the Find and Replace dialog box, click the Replace tab.
- In the Find what box, enter a decimal.
- In the Replace with box, enter an asterisk (*).
- Click Replace All if you are sure that you want to replace all symbols or characters in the selected range. Click Replace if you want to find and replace one by one in the selected range. Click Find All if you want to find all of the characters to be replaced first and then decide if you want to replace all. Click Find Next if you want to find characters one by one and decide if you want to replace each character by clicking Replace. After replacing all characters, Excel displays a dialog box with the number of replacements.
- In the Find what box, enter a comma.
- In the Replace with box, enter a decimal or period.
- Click Replace All if you are sure that you want to replace all symbols or characters in the selected range. Click Replace if you want to find and replace one by one in the selected range. Click Find All if you want to find all of the characters to be replaced first and then decide if you want to replace all. Click Find Next if you want to find characters one by one and decide if you want to replace each character by clicking Replace. After replacing all characters, Excel displays a dialog box with the number of replacements.
- In the Find what box, enter an asterisk (*).
- In the Replace with box, enter a comma.

Again, the result will be text, not numbers.

#### Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receivenew articles, join our email list.

#### More resources

How to Convert Text to Numbers in Excel

How to Highlight Errors, Blanks and Duplicates in Excel Worksheets

3 Excel Strikethrough Shortcuts to Cross Out Text or Values in Cells

How to Replace Blank Cells in Excel with Zeros (0), Dashes (-) or Other Values

Use Conditional Formatting in Excel to Highlight Dates Before Today (3 Ways)

#### Related courses

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to Power Query to Get and Transform Data

Microsoft Excel: New and Essential Features and Functions in Excel 365

Microsoft Excel: Introduction to Visual Basic for Applications (VBA)

VIEW MORE COURSES >

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.

Copyright 2022 Avantix** ^{®}** Learning

*Microsoft, the Microsoft logo,Microsoft Office and related Microsoft applicationsand logosare registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.*

Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca

## FAQs

### How do I change comma to decimal and vice versa in Excel? ›

**Click File > Options.** **On the Advanced tab, under Editing options, clear the Use system separators check box.** **Type new separators in the Decimal separator and Thousands separator boxes**. Tip: When you want to use the system separators again, select the Use system separators check box.

**How do I change to 5 decimal places in Excel? ›**

By using a button:

Select the cells that you want to format. **On the Home tab, click Increase Decimal or Decrease Decimal** to show more or fewer digits after the decimal point.

**How do I change comma to decimal and vice versa in Excel for Mac? ›**

**3, please follow these instructions:**

- Close the Excel application.
- Click on the Apple button.
- Select System Preferences.
- Select Language and Text.
- Click on Region.
- Under Numbers select Customize.
- Change the Decimal separator from a comma (,) to a full stop (.)
- Then click on Ok/Save.

**How do you convert decimal to vice versa? ›**

Decimals can be written in fraction form. To convert a decimal to a fraction, place the decimal number over its place value. For example, in 0.6, the six is in the tenths place, so we place 6 over 10 to create the equivalent fraction, 6/10.

**How do you round to 5 decimal places? ›**

Put simply, if the last digit is less than 5, round the previous digit down. However, **if it's 5 or more than you should round the previous digit up**. So, if the number you are about to round is followed by 5, 6, 7, 8, 9 round the number up. And if it is followed by 0, 1, 2, 3, 4 round the number down.

**How do you increment by 5 in Excel? ›**

Formula Method

The most obvious way to increment a number in Excel is to add a value to it. **Start with any value in cell A1, and enter "=A1+1" in cell A2 to increment the starting value by one**. Copy the formula in A2 down the rest of the column to continuously increment the preceding number.

**How do I change a number format from a comma to a dot? ›**

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.

**How do you change points in Excel? ›**

On the Format tab, in the Current Selection group, click Format Selection. Click Marker Options, and then under Marker Type, make sure that Built-in is selected. In the Type box, select the marker type that you want to use.

**How do you convert Comma Separated Values to lists? ›**

**Steps to convert a comma Separated String to ArrayList**

- Split the comma-delimited String to create String array - use String.split() method.
- Convert String Array to List of String - use Arrays.asList() method.
- Create an ArrayList by copying contents from fixed length list - Use ArrayList constructor.

**What is the shortcut of values in comma and 2 decimals? ›**

Comma style is a type of number format where it adds commas to large numbers, adds two decimal places (i.e. 1000 becomes 1,000.00), displays negative values in closed parentheses & represents zeros with a dash (–). The shortcut key for comma style is **ALT + HK**.

### How do you use a comma number format and no decimal places in Excel? ›

Once we click on the “Comma Style,” it will provide the comma-separated format value. **If we want to remove the decimal, we must click on the icon under “Number” on “Decrease Decimal.”** Once we remove the decimal, we can see below the value without decimals. Then, we must select cells under the “Amount” column.

**What are the steps in converting decimal to percent and vice versa? ›**

**How to Convert Decimal to Percent?**

- Step 1: Multiply the number by 100 by shifting the decimal point to the right by 2 places.
- Step 2: Add the percent symbol (%) to it. Example: 0.43 = 0.43 x 100% = 43%.

**How do you change a 3 digit number to a comma in Excel? ›**

Use 1000 separator - The 1000 separator will insert a comma every 3 digits, i.e. hundred, thousand etc.

**How do I quickly convert cells to numbers? ›**

**Use Paste Special and Multiply**

Select the cells that have numbers stored as text. On the Home tab, click Paste > Paste Special. Click Multiply, and then click OK. Excel multiplies each cell by 1, and in doing so, converts the text to numbers.

**How do you change the number format in Excel? ›**

**Available number formats in Excel**

- Select a cell or a cell range.
- On the Home tab, select Number from the drop-down. Or, you can choose one of these options: Press CTRL + 1 and select Number. Right-click the cell or cell range, select Format Cells… , and select Number. ...
- Select the format you want.

**What is the easiest way to convert fractions to decimal or vice versa? ›**

The easiest way to convert a fraction to a decimal is to **divide the numerator (the top of the fraction) by the denominator (the bottom of the fraction) by using a calculator**. The resulting answer will be the value of the fraction expressed as a decimal number.

**How do you convert fractions to decimals step by step? ›**

**You can convert a number from fractions and decimals easily by using the steps below.**

- Step 1: Find a number that when multiplied by the denominator makes 10, 100, or 1000. ...
- Step 2: Multiply both numbers by that number. ...
- Step 3: Take the new numerator and apply a 0 followed by a decimal point before it.

**How do you convert to a decimal? ›**

Convert from percent to decimal:

To change a percent to a decimal we **divide by 100**. This is the same as moving the decimal point two places to the left. For example, 15% is equivalent to the decimal 0.15. Notice that dividing by 100 moves the decimal point two places to the left.

**Why do we round 5 up instead of down? ›**

**Since the number following 5 is greater than zero**, this number should be rounded up; that is, the “Round 5 Even” rule should not be used. That's because this number is actually closer to 0.867 than it is to 0.866; there- fore rounding even in this case would introduce more error.

**When a decimal ends in 5 do you round up or down? ›**

To round a number look at the next digit in the right place, if the digit is less than 5, round down and **if the digit is 5 or more than 5, round up**.

### Why do we round up at 5 and not down? ›

**You are trying to round to the nearest full value**. If you are below 5, the lower one is closer, if you are above 5, the higher one is closer. However, if you are AT 5, they are equidistant. However, if the next digit is anything other than 0, like .

**Do I round up at 5? ›**

Here's the general rule for rounding: **If the number you are rounding is followed by 5, 6, 7, 8, or 9, round the number up**. Example: 38 rounded to the nearest ten is 40. If the number you are rounding is followed by 0, 1, 2, 3, or 4, round the number down.

**How do you add 5 numbers from different cells in Excel? ›**

**Use the SUM function to total a column**

- Click on the cell in your table where you want to see the total of the selected cells.
- Enter =sum( to this selected cell.
- Now select the range with the numbers you want to total and press Enter on your keyboard. Tip. You can enter the range address manually like =sum(B1:B2000) .

**How do I find the top 5 numbers in Excel? ›**

**Select cell B2, copy and paste formula =LARGE(A$2:A$16,ROWS(B$2:B2)) into the formula bar, then press the Enter key**. See screenshot: 2. Select cell B2, drag the fill handle down to cell B6, then the five highest values are showing.

**How do I change the number format in numbers? ›**

**Numbers**

- Select the cells or table you want to format.
- In the Format sidebar, click the Cell tab.
- Click the Data Format pop-up menu and choose an option: Number: Displays standard number formatting. Set the number of decimal places: In the Decimals field, type the number of decimal places you want to display.

**How do I calculate points in Excel? ›**

We **multiply the value of the financial instrument we wish to compute by the change in basis points**. Here we multiply C9 by C11 and divide by 10,000. 10,000 corresponds to 0.01 of 1%. 0.01*0.01=0.0001.

**How do I convert commas to separated data in Excel? ›**

**Try it!**

- Select the cell or column that contains the text you want to split.
- Select Data > Text to Columns.
- In the Convert Text to Columns Wizard, select Delimited > Next.
- Select the Delimiters for your data. ...
- Select Next.
- Select the Destination in your worksheet which is where you want the split data to appear.

**What are comma separated Values example? ›**

CSV is a delimited text file that uses a comma to separate values (many implementations of CSV import/export tools allow other separators to be used; for example, the use of a **"Sep=^" row as the first row in the *.** **csv file will cause Excel to open the file expecting caret "^" to be the separator instead of comma ","**).

**What is a comma separated list example? ›**

For example, **C{:,1}** is a comma-separated list if C has more than one row. A comma-separated list is produced for a structure array when you access one field from multiple structure elements at a time.

**What does Ctrl Shift 3 do in Excel? ›**

The shortcut to **format a cell as a date** is CTRL + SHIFT + 3, and to format it as time, the shortcut is CTRL + SHIFT + 2. If these shortcuts don't work on your keyboard, press CTRL + 1 while on the cell, and choose a format you would like to have.

### What does Ctrl Shift comma do? ›

...

Change or resize the font.

Ctrl-Shift-f | Change the font |
---|---|

Ctrl-Shift-p | Change the font size |

Ctrl-Shift-. (period) | Increase the font size |

Ctrl-Shift-, (comma) | Decrease the font size |

Ctrl-] | Increase the font size by 1 point |

**What is Ctrl Shift in Excel? ›**

Ctrl Shift-Enter **helps convert the data into an array format consisting of multiple data values in Excel**. It also supports differentiation between the regular formula and array formula in excel. There are two types of array formulas: one that returns a single result and the other that returns multiple results.

**Can we use comma for a number with 4 digits or less? ›**

When writing a four-digit numeral in digits (other than a date), **American writers never use a comma, but British writers usually do**.

**Where do you put the comma in a 6 digit number? ›**

A comma is placed **every third digit to the left of the decimal point** and so is used in numbers with four or more digits. Continue to place a comma after every third digit. For example: $1,000,000 (one million dollars)

**What are the steps to rounding decimals? ›**

Step 1: Circle the place value of the digit to be rounded. This is the rounding digit. Step 2: Look to the neighboring digit on the right. Step 3: a) If the neighboring digit is less than five (0 - 4), keep the rounding digit the same.

**How do you arrange decimal numbers from least greatest or vice versa? ›**

Correct answer:

**Look at the ones place first, ignoring the decimal**. The order from greatest to least is 2, 4, 5, 6, and 8. Because none of the numbers in the ones place are the same, it doesn't matter what the decimal will be on these numbers- the order will remain the same. The order is 2.1, 4.8, 5.2, 6.9, 8.5.

**What is the decimal vice versa of 2 5? ›**

Method 1: Writing 2/5 as a decimal using division method

To convert any fraction to decimal form, we just need to divide its numerator by denominator. This gives the answer as 0.4. So, 2/5 as a decimal is **0.4**.

**How do I change the formula separator in Excel? ›**

**From the File menu, select Options.** **Select Advanced.** **In the 'Editing options' group determine if 'Use system separators' is unselected**. If it is unselected, you can select it to have Excel use the Windows Regional settings or specify the 'Thousands separator' you want to use.

**How do I change the delimiter in Excel? ›**

To force it to use a different delimiter, proceed with the following steps: **Click File > Options > Advanced.** **Under Editing options, clear the Use system separators check box.** **Change the default Decimal separator**.

**Why is there a comma instead of decimal? ›**

as a separator between the dollars and cents. Some countries use a comma (,) instead of a decimal **to indicate that separation**. In addition, while the U.S. and a number of other countries use a comma to separate thousands, some countries use a decimal point for this purpose.

### How do I change formulas in multiple cells at once? ›

**Fill a formula down into adjacent cells**

- Select the cell that has the formula you want to fill into adjacent cells.
- Drag the fill handle. across the cells that you want to fill. ...
- To change how you want to fill the selection, click the small Auto Fill Options icon.

**What is the shortcut to change formulas in Excel? ›**

When you have a cell selected, **pressing the F2 key** puts the cell in Edit mode. If the cell contains a formula, you will see the formula in the cell and be able to edit it. This is the same as double-clicking the cell with the mouse.

**How do I import a text file into Excel 365? ›**

**Import a text file by opening it in Excel**

- Go to File > Open and browse to the location that contains the text file.
- Select Text Files in the file type dropdown list in the Open dialog box.
- Locate and double-click the text file that you want to open. If the file is a text file (.txt), Excel starts the Import Text Wizard.

**How do I import a text file into columns in Excel? ›**

The steps to import a TXT or CSV file into Excel are similar for Excel 2007, 2010, 2013, and 2016: Open the Excel spreadsheet where you want to save the data and click the Data tab. In the Get External Data group, click From Text. Select the TXT or CSV file you want to convert and click Import.

**How do I convert a column to a comma separated list in Excel? ›**

Type the formula **=CONCATENATE(TRANSPOSE(A1:A7)&",") in a blank cell adjacent to the list's initial data**, for example, cell C1. (The column A1:A7 will be converted to a comma-serrated list, and the separator "," will be used to separate the list.)

**What are the 11 data formats in Excel? ›**

**The following are the type of number formats available in Excel.**

- General. General is a number format selected as the default by excel for any number you type into the spreadsheet. ...
- Number. Number Format is exclusively used when you are working with numbers. ...
- Currency. ...
- Accounting. ...
- Date. ...
- Time. ...
- Percentage. ...
- Fraction.

**What is the shortcut key for number format in Excel? ›**

To do this | Press |
---|---|

Apply the Scientific number format with two decimal places. | Ctrl+Shift+Caret sign (^) |

Apply the Date format with the day, month, and year. | Ctrl+Shift+Number sign (#) |

Apply the Time format with the hour and minute, and AM or PM. | Ctrl+Shift+At sign (@) |

**How do you use commas with two decimal places? ›**

Comma style is a type of number format where it adds commas to large numbers, adds two decimal places (i.e. 1000 becomes 1,000.00), displays negative values in closed parentheses & represents zeros with a dash (–). The shortcut key for comma style is **ALT + HK**.