How to Convert European Number Format to US in Excel (4 Methods)

A number format is a standard way to present numerical values that can be easily understood by a wide audience. Different regions around the world use various number formats, each with its own conventions. Among these, the European and US number formats stand out. In this article, we’ll explore how to convert European number formats to the US format in Excel, complete with clear illustrations.


Difference Between European and US Number Formats

The primary distinction between European and US number formats lies in the use of decimal and thousand separators. Here’s how they differ:

  1. European Format:
    • Decimal separator: Comma (,)
    • Thousand separator: Decimal point (.)
    • Example: 1.010,53
  2. US Format:
    • Decimal separator: Decimal point (.)
    • Thousand separator: Comma (,)
    • Example: 1,010.53

We’ll work with a dataset of numbers in the European format and explore different techniques to convert them to the US format.


Method 1 – Using the NUMBERVALUE Function:

  • Add a new column to the right of your dataset.

  • In cell C5, enter the following formula:

=NUMBERVALUE(B5,",",".")

This formula converts the European number in cell B5 to a US-compatible numeric value.

Apply NUMBERVALUE Function to Convert Numbers from European to US Format

  • Press Enter and drag the Fill Handle icon to apply the formula to other cells.

While the commas (,) are removed and decimal points added, there are no thousand separators yet.

To add thousand separators:

  • Press Ctrl + 1 to open the Format Cells window.
  • Choose the Number category from the Number tab.
  • Tick the Use 1000 Separator (,) option.

Change number format to Convert Numbers from European to US Format

  • Adjust the decimal places as needed.
  • Click OK.

The numbers are now successfully converted to the US format.

Read More: How to Convert Percentage to Decimal in Excel


Method 2 – Using the SUBSTITUTE Function:

  • Insert the following formula in cell C5:

=SUBSTITUTE(SUBSTITUTE(B5, ".", "" ), ",", ".")+0

SUBSTITUTE Function to Convert Numbers in Excel

Formula Explanation:

  • SUBSTITUTE(B5, “.”, “” )

Removes decimal points.

Result: 131121,52

  • SUBSTITUTE(SUBSTITUTE(B5, “.”, “” ), “,”, “.”)

Replaces commas with decimal points.

Result: 131121.52

  • SUBSTITUTE(SUBSTITUTE(B5, “.”, “” ), “,”, “.”)+0

Adding 0 converts the result from General to Number format.

Result: 131121.52

  • Press Enter and drag the Fill Handle icon downward to apply the formula to other cells.

Note that we’ve already added commas as thousand separators (as shown in Method 1).

Read More: How to Convert Exponential Value to Exact Number in Excel


Method 3 – Using Excel’s Text to Columns Wizard to Convert Number Format from European to US:

In this method, we’ll leverage the built-in “Text to Columns” feature in Excel to convert numbers from the European format to the US format.

Copying Data:

  • First, copy the numbers from the European Number column to the US Number column.

Accessing Text to Columns:

  • Select the data in the US Number column.
  • Go to the Data tab and click on Text to Columns.

Text to Columns to Convert Number from European to US Format

  • The Convert Text to Columns Wizard window will appear. 

Configuring Settings:

  • Choose the Fixed Width option and click Next.

  • Skip the second step of the wizard.
  • Click Advanced in the third step.

Advanced feature to Convert number in Excel

  • In the Advanced Text Import Settings window:
    • Set a comma (,) as the decimal separator.
    • Set a decimal point (.) as the thousands separator.

  • Confirm the settings by pressing OK.

Finishing the Conversion:

  • Finally, click Finish in the Convert Text to Columns Wizard.

  • Examine the dataset.

You’ll notice that the given numbers have been successfully converted into the US format.


Method 4 – Converting Number Formats Using VBA Macro:

Now, let’s use a VBA (Visual Basic for Applications) macro to convert numbers from the European format to the US format. This VBA code works in two steps:

Accessing VBA Window:

  • Go to the sheet name at the bottom of the Excel sheet.
  • Right-click and choose View Code from the context menu.

Enter VBA window to convert number format in Excel

  • The VBA window will appear.

Creating a Module and Adding Code:

  • Click on Insert and choose Module.

  • Add the following VBA code to the module window:
Sub Euro_to_US()
Selection.Replace What:=".", Replacement:="XXXXX", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="XXXXX", Replacement:=",", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
End Sub

Convert Number from European to US Format Using VBA Macro

Running the VBA Code:

  • Select a range of data.
  • Press the F5 key to execute the VBA code.

  • Observe that decimal points have been removed.
  • Press F5 again to complete the conversion.

  • The data is now in the US format.

Download Practice Workbook

You can download the practice workbook from here:


Related Articles


<< Go Back to Change Number Format | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo