How To Change Shape Of Data Points In Excel

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 land or region, Excel may brandish 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 earth. You lot 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 organization settings (Regional Settings in the Command Panel) and Excel Options.

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

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

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

Recommended article: 10 Dandy Excel Pin Table Shortcuts

Exercise you want to learn more than about Excel? Bank check out our virtual classroom or live classroom Excel courses >

Changing commas to decimals past irresolute global system settings

Information technology'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 French republic, the comma is used as the decimal separator based on system settings with French republic as the region.

Beneath is the Control Panel in Windows 10:

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

1. In the Start a search box on the bottom left of the screen, blazon Control Panel. The Command Panel appears.
2. Click Clock and Region. This may appear as Languages and Regional Standards.
3. Below Region, click Alter Engagement, Fourth dimension, or Number Formats. A dialog box appears. If you change the region, Excel (and other programs) volition employ settings based on the selected region then U.s. would utilise periods as decimals and commas as thousands separators.
4. If yous don't want to change the region, click Boosted settings. A Customize Format dialog box appears.
5. Click the Numbers tab.
6. Select the desired options in Decimal symbol and Digit grouping symbol. You can as well modify other options in this dialog box (such as the format of negatives).
7. Click OK twice.

Below is the Customize Format dialog box:

Keep in mind that this volition 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 brandish based on their organization settings.

Changing commas to decimals and vice versa by changing Excel Options

Y'all tin also change Excel Options so that commas display as the group or thousands separator instead of decimals and vice versa. This option will affect whatever workbook you open after you accept made this change.

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

1. Click the File tab in the Ribbon.
2. Click Options.
3. In the categories on the left, click Avant-garde.
4. Uncheck Use organisation separators in the Editing expanse.
5. In the Decimal separator box, enter the desired character such as a decimal or period (.).
6. In the Thousands separator box, enter the desired character such as a comma (,).
7. Click OK.

Below is the Options dialog box where Use system separators has been turned off and a decimal and comma accept 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 some other jail cell or cavalcade to catechumen separators. This works well if the information is text with separators that are not used in the electric current organisation settings. If you write a formula to catechumen text to numbers, yous can keep the original values as well. In Excel 2013 or subsequently, you tin can use the NUMBERVALUE function to convert the text to numbers.

The NUMBERVALUE function has the post-obit arguments:

Text – The text to convert to a number.

Decimal_separator – The grapheme used in the original cell to split the integer and fractional part of the event.

Group_separator – The graphic symbol used in the original cell to split groupings of numbers, such as thousands from hundreds and millions from thousands.

The function is entered every bit follows:

=NUMBERVALUE(Text,Decimal_separator,Group_separator)

For example, if you accept 2.500.300,00 in A1 and your device is set to U.s. in the Regional Settings, yous could enter the following formula in B1:

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

The value that is returned will normally appear every bit a number without formatting. Y'all can and so apply appropriate formatting by pressing Ctrl + i 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 tin be used to clean up and / or reformat data. It is not a formula, so if the original data changes, you lot'll need to rerun Wink Fill.

In the example below, the numbers entered in column A are formatted using organization settings (United states in this example). In column B, we have entered sample information in B2 and B3 with different separators (periods for the group separator and commas for the decimal separator). Information technology's unremarkably all-time to enter a few examples to show Excel the blueprint. You lot'll demand data with a blueprint to the left in social club to utilise Flash Fill up. One time you accept entered a few examples of the design in cells to the right of the original data, in the cell below the samples (in this case in jail cell B4) press Ctrl + E. Flash Make full volition make full in the remaining cells until it reaches the end of the data set.

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

For more data, cheque out How to Use Flash Make full in Excel (4 Ways with Shortcuts).

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

You can employ the Replace command to replace commas with decimals or vice versa In a range of cells on a worksheet. It's important to empathize 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 supersede on a copy of the data.

To change commas to decimals using Replace:

1. Select the range of cells in which you lot desire to supervene upon commas with decimals. Y'all may select a range of cells, a column or columns or the entire worksheet.
2. Printing Ctrl + 1 or correct-click and select Format Cells. A dialog box appears.
3. Click the Number tab.
4. In the categories on the left, click Text and so click OK. Notably, the data volition exist text non numbers.
5. Click the Habitation tab in the Ribbon and in the Editing group, click Find & Select. A driblet-down carte appears.
6. Click Replace. Alternatively, printing Ctrl + H. A dialog box appears.
7. In the Find and Replace dialog box, click the Replace tab.
8. In the Find what box, enter a comma (the grapheme that you want to notice).
9. In the Replace with box, enter a decimal or period (the character that you want to replace).
10. Click Supercede All if you are sure that y'all want to replace all symbols or characters in the selected range. Click Supercede if you want to find and replace one by one in the selected range. Click Discover All if you desire to notice all of the characters to exist replaced first and then decide if you want to supersede all. Click Detect Next if you want to notice characters one by one and make up one's mind if you desire to supervene upon each grapheme by clicking Replace. Later 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 Observe what box and a decimal (.) in the Replace with box:

In the previous instance, only commas were inverse to decimals simply you may have data that has both commas and decimals (similar 1,475.55). In this example, y'all would need to Replace multiple times.

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

1. Select the range of cells in which you want to replace commas with decimals and decimals with commas. You lot may select a range of cells, a column or columns or the entire worksheet.
2. Press Ctrl + i or correct-click and select Format Cells. A dialog box appears.
3. Click the Number tab.
4. In the categories on the left, click Text and and then click OK. Notably, the information will be text not numbers.
5. Click the Home tab in the Ribbon and in the Editing group, click Find & Select. A drop-downwards menu appears.
6. Click Replace. Alternatively, press Ctrl + H. A dialog box appears.
7. In the Discover and Replace dialog box, click the Supercede tab.
8. In the Find what box, enter a decimal.
9. In the Supervene upon with box, enter an asterisk (*).
10. Click Replace All if you lot are sure that you want to replace all symbols or characters in the selected range. Click Replace if you desire to detect and replace one by one in the selected range. Click Find All if yous want to detect all of the characters to be replaced first and then decide if you want to supplant all. Click Find Adjacent if yous want to find characters one by one and decide if you desire to supercede each character by clicking Replace. After replacing all characters, Excel displays a dialog box with the number of replacements.
11. In the Observe what box, enter a comma.
12. In the Supplant with box, enter a decimal or period.
13. Click Replace All if you are sure that you lot want to replace all symbols or characters in the selected range. Click Replace if you want to find and replace ane by one in the selected range. Click Detect All if yous want to detect all of the characters to exist replaced first and then make up one's mind if you desire to replace all. Click Notice Side by side if you want to discover characters 1 by i and decide if you want to supercede each character by clicking Replace. After replacing all characters, Excel displays a dialog box with the number of replacements.
14. In the Find what box, enter an asterisk (*).
15. In the Replace with box, enter a comma.
16. Click Replace All if you lot are sure that you lot want to supersede all symbols or characters in the selected range. Click Replace if yous want to detect and supercede one past one in the selected range. Click Find All if you want to find all of the characters to exist replaced first and so decide if you desire to replace all. Click Find Next if you want to detect characters 1 past i and decide if you want to replace each character by clicking Replace. Afterwards replacing all characters, Excel displays a dialog box with the number of replacements.

Again, the result will be text, not numbers.

Subscribe to get more manufactures similar this i

Did y'all find this article helpful? If y'all would like to receive new manufactures, join our email list.

More than resources

How to Catechumen Text to Numbers in Excel

How to Highlight Errors, Blanks and Duplicates in Excel Worksheets

three Excel Strikethrough Shortcuts to Cantankerous Out Text or Values in Cells

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

Use Provisional Formatting in Excel to Highlight Dates Before Today (iii Means)

Related courses

Microsoft Excel: Intermediate / Advanced

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

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

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 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada (some in-person classroom courses may besides exist delivered at an alternating downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom onsite training or an online course on a appointment that'south user-friendly for you.

Copyright 2022 Avantix ^® Learning

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, The states and other countries. All other trademarks are the belongings of the registered owners.

Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Edifice), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca

Source: https://www.avantixlearning.ca/microsoft-excel/how-to-change-commas-to-decimal-points-and-vice-versa-in-excel/

Posted by: haygoodmarsou.blogspot.com

Share this post