Change Character Separating Thousands or Decimals in Excel

In many countries such as the USA, UK, and Australia, a comma (,) is used for separating groups of thousands, and a period (.) is used as a decimal separator. Thus, in Excel, a currency value of two thousand and thirty-five dollars and twenty-five cents is displayed as 2,035.25. We will refer to this number format as the US number format.

However, in most European countries, a comma (,) is used as the decimal separator and a period (.) is used as the thousands separator. Thus, in Excel, the same currency value we referred to before would be displayed as 2.035,25 or 2 035,25 (groups of three digits separated by a thin space). We will refer to this number format as the European number format.

In this tutorial we will look at 5 different methods we can use to change numbers from the European number format to the US number format and vice versa.

Method 1: Use the Find and Replace feature in Excel

Change European number format to the US number format

In this method, we use the following steps to change the character separating thousands from the period (.) to comma (,) and the character separating decimals from comma (,) to decimal (.).

We will use the following dataset that has the Price column in European number format to show how the method works.

  1. We first need to remove the period (.) as the thousands separator. First highlight column B with price values and press Ctrl + H to open the Find and Replace dialog box. Alternatively, click Home >> Editing >> Find & Select Arrow >> Replace.
  1. In the Find and Replace dialog box, type a period (.) in the Find what box and leave the Replace with box blank and click Replace All.
  1. Click OK on the information message box that pops up. The period (.) separator is removed:
  1. We now need to replace the comma (,) as the decimals separator with a period(.). In the Find and Replace dialog box, delete the period in the Find what box and type in a comma (,). In the Replace with box type a period (.) and click Replace All:
  1. Click OK on the information message box that pops up and click Close on the Find and Replace dialog box to close it. The commas in the prices are replaced with periods and the prices are now right-aligned meaning that Excel now recognizes them as numbers.
  2. With column B still selected, click Home >> Number >> Comma Style.

The dataset is now in the US format: the comma (,) is now the thousands separator and the period (.) is the decimals separator.

Method 2: Import data through the Windows Notepad

In this method, we use the following steps to change the character separating thousands from the comma (,) to period (.) and the character separating decimals from period (.) to comma (,).

We will use the following dataset that has the Price column in US number format to show how the method works.

  1. Select the dataset and press Ctrl + C to copy the dataset.
  2. Open the Windows Notepad app and press Ctrl + V to paste in the dataset.
  1. First, we need to temporarily replace the comma (,) thousands separator with a star (*) placeholder. To open the Replace dialog box press Ctrl + H or click File >> Replace on the Notepad menu.
  2. In the Replace dialog box type comma (,) in the Find what box and a star (*) in the Replace with box, and then click Replace All. All the commas are replaced with stars:
  1. Next is to replace the period (.) decimal separator with a comma (,). In the Replace dialog box, clear the Find what box and type in a period (.). Clear the Replace with box and type in a comma (,) and then click Replace All. All the periods are replaced with commas.
  1. The next step is to replace the star (*) placeholders with periods (.). In the Replace dialog box, clear the Find what box and type in a star (*). Clear the Replace with box and type in a period (.) and then click Replace All. All the stars are replaced with periods.
  1. Click the X on the Replace dialog box to close it. Select the data and press Ctrl + C to copy it.
  2. Activate the new Excel worksheet in which we want to paste the data. Select cell A1 and press Ctrl + V to paste the data. The Price column is in the European number format.

Method 3: Temporarily change the Excel settings

Suppose we are using Excel that has the default Europe number format settings and we want to import data that has the US number format. We can temporarily change the Excel settings on that external source before importing the data.

We will use the following dataset to demonstrate how this method works:

We use the following steps:

  1. On the eternal computer that has the Excel data we want to import, in the Excel file, click File >> Options to open the Excel Options dialog box:
  1. In the Excel Options dialog box, select Advanced, then remove the checkmark from the Use system separators checkbox. Type a comma (,) in the Decimal separator box, and type a period (.) in the Thousands separator box. Then click OK to apply the settings.

When we now look at our dataset, the data has changed to the European number format:

We can now import the data.

After importing the data, it is strongly advised to change the Excel settings in the source data to the default settings.

Method 4: Temporarily change the Windows regional settings

Suppose we are in a country that uses the US number format and our Windows regional settings are set accordingly. We want to prepare a financial report that includes screen captures of data in Excel for an audience living in Germany. We can temporarily change our Windows regional settings such that a comma becomes the decimals separator and a period becomes the thousands operator.

We use the following steps:

  1. Open Start >> Control Panel >> Clock, Language, and Region.
  2. In the Region and Language dialog box, select Germany in the Format drop-down box and then click Apply:
  1. Click on Additional settings to open the Customize Format dialog box.

We can see that the Decimal symbol and Digit grouping symbol have changed accordingly. Click OK and exit the Control Panel.

Excel will now pick from these changed settings.

We can then take screen captures of the data we want to incorporate into our report.

After we are done with what we wanted to do, It is strongly recommended to go back to the Control Panel and change back to the default settings.

Method 5: Use formulas

Formula 1

In this method, we use the NUMBERVALUE function in a formula to change the character separating thousands or decimals in Excel. In this case, we want to change the Price in European number format to US number format.

The NUMBERVALUE function converts text to numbers in a locale-independent number.

We will use the following dataset to explain this method.

We use the following steps:

  1. Select cell D2 and type in the formula
=NUMBERVALUE(B2,",",".") as below:
  1. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column:
  1. With range D2:D4 still selected, click Home >> Number >> Comma Style.

The Price is converted to the US format:

Formula 2

In this second formula, we use nested SUBSTITUTE functions to convert the European number format to the US number format.

The SUBSTITUTE function replaces existing text with new text in a text string.

With the dataset we used in Formula 1, we do the following:

  1. Select cell D2 and type in the formula:
=SUBSTITUTE(SUBSTITUTE(B2, ".", "" ), ",", ".")+0
  1. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column:
  1. With range D2:D4 still selected, click Home >> Number >> Comma Style.

The characters separating thousands or decimals are now changed:

Formula 3

In this third formula, we use SUBSTITUTE and FIXED functions to convert a US number format to a European number format.

We use the following dataset to demonstrate how the formula works:

We use the following steps:

  1. Select cell D2 and type in the formula:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FIXED(B2,2,FALSE),".","$"),",","."),"$",",")
  1. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column. The Price is converted to European format:

Conclusion

When we import data from external sources, we can come across data that has characters separating thousands or decimals that are different from ours. We need to change those separators to the ones that we can work with.

The US number format uses comma (,) as the thousands separator and period (.) as the decimal separator. The European number format uses period (.) as the thousand separator and comma (,) as the decimal separator.

In this tutorial, we have explored 5 different methods that we can use to change the character separating thousands or decimals in Excel from the US number format to the European number format and vice versa.

The methods are: use the Excel Find and Replace Feature, import data via Windows Notepad, temporarily change the Excel settings, temporarily change the Windows regional settings, and use formulas.