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.
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
How to Convert European Number Format to US in Excel: 4 Ways
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
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,",",".")
- 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.
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 Percentage to Decimal in Excel
2. Use of SUBSTITUTE Function
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
- 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 Exponential Value to Exact Number in Excel
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.
- 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 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.
4. Convert Number Formats Using VBA Macro
Now, we will use VBA code to convert numbers from European to US Format. This VBA code works 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.
- 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
- 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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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 give your suggestions in the comment box.
Related Articles
- [Solved] Excel Number Stored As Text
- Number Format Is Not Working in Excel
- [Solved!] Long Numbers Are Displayed Incorrectly in Excel
<< Go Back to Change Number Format | Number Format | Learn Excel
