Excel Import CSV Decimal Separator: How to Set It

How-to basics · Updated November 18, 2026

To import a CSV into Excel with the intended decimal separator, use Data > From Text/CSV, choose the correct field delimiter, and—if the numbers do not parse correctly—set the affected column’s type with the file’s locale using Power Query’s Using Locale option. For a dependable result, match the locale to the number format in the CSV, not simply to the language of your copy of Excel.

What controls a CSV decimal separator in Excel?

A CSV file is plain text: it contains characters and separators, not Excel cell formats. A value such as 12,50 is just text in the file until Excel interprets it. Whether Excel turns that text into the numeric value twelve and a half depends on how the file is opened or imported, the column’s data type, and the regional number conventions being used.

Many English-language settings use a period as the decimal separator and a comma between fields: Product,Price followed by Pen,12.50. In other locales, a comma can mark the decimal and a semicolon can separate fields: Product;Price followed by Pen;12,50. These are common patterns, not universal rules. A CSV creator can use different delimiters or formats, so inspect the file or its source rather than assuming its conventions.

Keep three separate choices in mind:

  • Field delimiter: the character separating columns, such as a comma, semicolon, or tab.
  • Decimal separator: the character separating the whole-number and fractional parts, such as a period or comma.
  • Thousands separator: an optional grouping character, such as a comma, period, or space.

Choosing the wrong field delimiter can put a whole row into one column. Choosing the wrong decimal convention can leave a price as text, create an error, or produce a misleading number. Fixing the right issue is easier when you identify which of these separators is involved.

Import the CSV through Excel’s data import

On Excel for Microsoft 365 on Windows, the most controllable starting point is Data > From Text/CSV. Browse to the file and select Import. Excel displays a preview where you can check whether the columns are split correctly and whether values look as expected. If the preview shows one column instead of several, choose the actual delimiter in the delimiter control before loading the file.

When the preview is correct, choose Load to bring the data into the workbook. If you need to set or check the number format before it reaches the sheet, choose Transform Data instead. That opens Power Query, where you can specify the data type and locale for a column. This is usually safer than double-clicking a CSV, because you can review the import choices instead of relying entirely on automatic interpretation.

  1. Open a blank workbook, or the workbook where the imported data should go.
  2. Select Data > From Text/CSV and choose the CSV file.
  3. Check the preview and select the delimiter that actually divides the fields.
  4. Select Transform Data if decimal interpretation needs adjustment.
  5. Set the relevant column’s type and locale, review the preview, then select Close & Load.

Excel’s direct-open behavior can differ from this controlled import. Opening a CSV by double-clicking it, or using File > Open, may make Excel infer delimiters and data types from regional settings. If the same file must be imported repeatedly, a saved Power Query process is more consistent: you can refresh it when the source file is updated and retain the chosen transformation steps.

Set the column’s locale in Power Query

In the Power Query Editor, select the column containing the values. Use Transform > Data Type > Using Locale (the equivalent command may also appear through the column’s type menu). In the dialog, choose the intended type, usually Decimal Number, and the locale that matches the text’s number convention. For example, choose a comma-decimal locale for data written as 12,50, rather than assuming that Excel’s display language determines how the source should be read.

The locale affects how Power Query reads the text, including the meaning of decimal and grouping marks. Consider the value 1.234,56, commonly used to mean one thousand two hundred thirty-four and 56 hundredths in some locales. A locale that expects a period decimal and comma grouping may not read it the same way. After choosing the locale, inspect the preview: the value should appear as a number, not as text or an error. Then load it into the worksheet.

Use the locale for the source data, not a guess based only on the country where the workbook was created. A file exported from a European accounting system could contain comma decimals even when it is being imported on a US-configured computer. If you are unsure, check the exporting system’s documentation or compare a known value with its intended amount.

Decimal separators and field delimiters are different

A frequent source of confusion is that the same character can be used for different jobs. In Pen;12,50, the semicolon separates the two fields, while the comma is part of the decimal value. If Excel is told that the comma is the field delimiter, it may split the number into separate pieces. If it is told that the semicolon is the delimiter, the product and price remain in their intended columns.

Quoted CSV fields can contain delimiters without splitting into extra columns. For example, a properly quoted field can contain a comma as part of an address or description. Excel’s import preview helps reveal whether quoting and delimiters have been handled properly. Avoid trying to fix every comma or period with Find and Replace across the whole file: doing so can change field separators, text, or thousands marks as well as decimals.

Thousands separators need attention too. A source value such as 1,234.56 uses a comma for grouping and a period for decimals in one common convention. A different locale may write the same amount as 1.234,56. If the data contains grouped numbers, select the matching locale and verify representative values, including both small and large amounts.

Change Excel’s separator settings when appropriate

Excel for Microsoft 365 on Windows has a workbook-independent setting for the separators Excel uses. Go to File > Options > Advanced. Under Editing options, clear Use system separators to enter a decimal separator and a thousands separator of your choice. Select the checkbox again to use the system settings. This can help when you regularly enter or view numbers using a convention different from Windows’ regional settings.

This setting is not a substitute for checking an import. It changes Excel’s separator behavior; it does not rewrite the source CSV, and it does not tell you which convention the file’s author intended. Power Query’s locale setting is more explicit for a particular imported column. If you change Excel’s separators, remember that the setting can affect number entry and display in other workbooks too.

On Excel for Mac, separator controls are generally under Excel > Preferences > Advanced, with the option to use system separators. Menu wording and available text-import features can vary by Excel version. Excel for the web also has different import and settings capabilities from the Windows desktop app; if you need Power Query’s locale controls and do not see them, use Excel for Microsoft 365 on Windows or check the options available in your version of Excel.

Why numbers import as text or change unexpectedly

If a value remains left-aligned or does not behave like a number, it may have been imported as text. Changing its cell format to Number does not necessarily convert text into a numeric value: formatting controls how a number is displayed, while conversion determines whether Excel has a number to format. Reimporting through Power Query with the correct locale is often the clearest remedy for a whole column.

Check these common causes before editing the data manually:

  • Wrong delimiter: the preview does not show the expected columns. Select the actual field separator.
  • Wrong locale: decimal-comma values produce errors or stay as text. Choose a locale that reads the source convention.
  • Grouping marks: values contain both decimal and thousands separators. Check a large value as well as a small one.
  • Automatic type detection: a column may be assigned an unsuitable type. Set the type deliberately in Power Query.
  • Mixed formats: some rows use periods and others commas. Identify the source of the inconsistency before converting the entire column.

There is another important exception: not every column that looks numeric should become a number. Product codes, postal codes, and account identifiers can contain leading zeros. If 00127 is an identifier, converting it to a number can turn it into 127, losing meaningful characters. Import such columns as text. The same caution applies to long identifiers, for which numeric storage may not preserve the original representation.

Make repeat imports more reliable

For a one-time import, use the preview and verify several values before loading. For a recurring report, use Power Query and keep the transformation steps with the workbook. On refresh, check that the source file still has the same columns and conventions; if its export settings change, a previously correct locale or delimiter may no longer be right.

Before relying on imported totals, compare a few known values and a simple total with the source system. Keep an untouched copy of the original CSV if it is important data. CSV files do not preserve Excel formatting, formulas, or Power Query steps; those belong to the workbook or the system that produced the export. Treating import and display as separate tasks helps avoid “fixes” that only make a column look right without making its values usable.

Frequently Asked Questions

How do I make Excel use a comma as the decimal separator?
For Windows, open File > Options > Advanced, clear Use system separators, and enter a comma as the decimal separator. For a CSV import, set the column’s locale in Power Query as well if the source uses comma decimals; this makes the interpretation explicit.

Why does Excel open my CSV in one column?
Excel has probably inferred the wrong field delimiter. Import with Data > From Text/CSV and select the character that separates the fields, such as a semicolon or comma. Do not choose the decimal mark as the delimiter just because it appears frequently in the file.

Can I change the decimal separator after importing?
Yes, but changing a cell’s number format only changes the display of values that are already numeric. If the imported values are text, convert them using the correct locale in Power Query or another deliberate conversion method, then check the results.

Does changing the decimal separator change the CSV file?
No. Excel’s separator preferences affect how Excel handles or displays values; they do not edit the original CSV on disk. If you need a new CSV with different separators, export or save a separate copy and verify its contents and delimiter.

Which locale should I choose in Power Query?
Choose the locale whose number conventions match the text in the source column. For example, use a comma-decimal locale for values such as 12,50. Verify the preview against a known amount, especially when the file also uses a thousands separator.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.