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

A Number format is a standard to present a number that is understandable to mass people. In different regions of the world, different types of number formats are available. Those formats vary from one form to another. The European and US number formats are among them. In this article, we will how to convert European number format to US in Excel with proper illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Difference Between European and US Number Formats

The main difference between European and US number formats is identified as the decimal, thousand separators. Most of the European count use commas as the decimal separator and decimal points as the thousand separators. On the other hand in US formatting, the decimal point is used as the decimal separator, and the comma is used as the thousand separators.

We want to present one thousand ten and fifty-three cents in the below ways using the mentioned formats.

European Format: 1.010,53

US Format: 1,010.53


4 Methods to Convert European Number Format to US Format in Excel

In this article, we will show multiple ways to convert numbers from European format to US Excel. We have a dataset of numbers in the European format.

We will apply different techniques in the below section to convert those numbers into US format.


1. Use the NUMBERVALUE Function to Convert Numbers from European to US Format

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

In this method, we will use the NUMBERVALUE function to convert a number from one format to another in Excel.

📌 Steps:

  • First, add a new column on the right side.

  • Go to Cell C5 and put the below formula.
  =NUMBERVALUE(B5,",",".")

Apply NUMBERVALUE Function to Convert Numbers from European to US Format

  • Now, press the Enter button and drag the Fill Handle icon.

We can see commas (,) have been removed and decimal points added. But there are no thousand separators.

  • Press Ctrl + 1 to enter the Format Cells window.
  • Choose the Number option from the Number tab.
  • Tick on the Use 100 Separator (,) option.

Change number format to Convert Numbers from European to US Format

We can also increase or decrease the Decimal places from the marked box.

  • Finally, press the OK button.

We can see numbers are converted into US format successfully.

Read More: How to Convert Text to Number Using Formulas in Excel


2. Use of SUBSTITUTE Function

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

We will use the combination of two SUBSTITUTE functions to make a formula that will convert numbers from European to US in Excel.

📌 Steps:

  • Just insert the following formula on Cell C5.
=SUBSTITUTE(SUBSTITUTE(B5, ".", "" ), ",", ".")+0

SUBSTITUTE Function to Convert Numbers in Excel

  • Just insert the following formula on Cell C5.
  • Then, press the Enter button and drag the Fill Handle icon downwards.

One more thing that needs to add is that we already added commas as the thousand separators as shown in Method 1.

Formula Explanation:

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

Here, decimal points are replaced by blanks.

Result: 131121,52

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

Here, commas are replaced by decimal points.

Result: 131121.52

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

This converts the result from General to Number.

Result: 131121.52

Read More: How to Convert Decimal to Whole Number in Excel (10 Easy Ways)


Similar Readings


3. Use of Excel Text to Columns Wizard to Convert Number Format from European to US

Here, we will use Text to Columns which is a default feature of Excel. This feature will covet numbers from European to US format.

📌 Steps:

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

  • Select the data of the US Number column.
  • Go to Text to Columns from the Data tab.

Text to Columns to Convert Number from European to US Format

  • The Convert Text to Columns Wizard window appears. 
  • Tick the Fixed width option, then press the Next option.

  • Skip the 2nd step of the Convert Text to Columns Wizard.
  • Click the Advanced option of the 3rd step of the Convert Text to Columns Wizard.

Advanced feature to Convert number in Excel

  • Advanced Text Import Settings window appears.
  • Put a comma on the box of the Decimal separator.
  • Again, put a decimal point on the box of the Thousands separator.

The comma is used as the decimal separator and the decimal point as the thousand separators of the given dataset.

  • Finally, press the OK button.
  • Lastly, press the Finish button of Convert Text to Columns Wizard.

  • Look at the dataset.

We can see given numbers have been converted into US format.

Read More: How to Bulk Convert Text to Number in Excel (6 Ways)


4. Convert Number Formats Using VBA Macro

Now, we will use VBA code to convert numbers from European to US Format. This VBA code work in two steps.

📌 Steps:

  • First, go to the sheet name in the bottom section of the sheet.
  • Choose the View Code option from the Context Menu.

Enter VBA window to convert number format in Excel

  • The VBA window appears.
  • Choose the Module option from the Insert tab.

  • Put the following VBA code on 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

  • Now, select a range of data. Then, press the F5 button to run the VBA code.

Look at the dataset. Decimal points have been removed.

  • Again, press the F5 button to run the code.

We can see data already converted into US format.

Read More: How to Convert Text to Number with Excel VBA (3 Examples with Macros)


Conclusion

In this article, we described 4 methods to convert numbers from European to US in Excel. We also added a VBA code. I hope this will satisfy your needs. Please have a look at our website ExcelDemy and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo