Mehedi Hasan
Often, we need to use large numbers in international number format in our Excel sheet. It becomes difficult to read those numbers if the formatting is not applied accurately. In this article, I will show you some examples to change the international number format in Excel. Hence, go through the article carefully and save your time.
How to Change International Number Format in Excel: 4 Examples
However, the international number format may contain various types of items. It may include general number format, currency format, date format, and many more. In this section, I have used European number format and the US number format in order to interchange themselves. For The purpose of demonstration, I have chosen four suitable examples and the following dataset to make you understand how to change international number format in Excel easily.
1. Converting European Number Format to US Number Format
Generally, there are small differences between international number formats. For example, I will show you the conversion of the European number format to the US number format. They have minor differences in the format. In order to complete the operation, I have used the NUMBERVALUE function. Hence, follow the steps below.
📌 Steps:
- Firstly, select the Cell C5 and write the following formula.
=NUMBERVALUE(B5,",",".")
- Secondly, press the Enter key and use the AutoFill tool to apply the formula to the entire column.
- Thirdly, select the entire column and right-click to select Format Cells from the dialog box.
- From the Format Cells dialog box, click Number from the Category list, and check Use 1000 Separator().
- Now, press OK.
- Finally, you will get your desired result.
2. Converting US Number Format to European Number Format
Similarly, you can convert the US number format to the European number format. But in this case, I have used the SUBSTITUTE function and the FIXED function in order to complete the operation. Similarly, you have to go through the steps below.
📌 Steps:
- Firstly, select Cell C5 and write the following formula.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FIXED(B5,2,FALSE),".","$"),",","."),"$",",")
Formula Breakdown
- Firstly, FIXED(B5,2,FALSE) takes the value from B5 and returns the value up to two decimal places without any commas.
- (SUBSTITUTE(FIXED(B5,2,FALSE),”.”,”$”) function takes the output of the previous function as the main text to replace and replaces every point (.) with a $ sign.
- Then (SUBSTITUTE(FIXED(B5,2,FALSE),”.”,”$”),”,”,”.”) function takes the output and returns after replacing every comma with a dot.
- Finally, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FIXED(B5,2,FALSE),”.”,”$”),”,”,”.”),”$”,”,”) function replaces every $ sign with a comma and returns the final output.
- After that, press the Enter button and use the AutoFill tool.
- Finally, the converted data will appear in the following image.
Read More: How to Change Comma to Dot in Excel
3. Changing International Currency Format
Nowadays, trade around the world has converted our globe into a global village. So, international business is seeing a boom that has not been observed before. This connectivity and trade-related aspects made currency conversion more frequent. In this part, I will demonstrate to you how to change the international currency format. For example, I have chosen some random amount of Euro and US Dollars as currencies. The dataset is shown below.
📌 Steps:
- First of all, select the entire column and right-click to open the Format Cells.
- From the Format Cells dialog box, select Currency as Category and then select Euro as Symbol.
- Finally, you will get your desired currency as output. Similarly, I have got the output for US Dollar.
4. Changing International Date Format in Excel
Usually, the date format is almost the same in most regions. But it also varies in some regions. For the purpose of demonstration, I have selected Germany from the European region and the United States from the American region to make a comparison between the two date formats. However, follow the steps below to complete the operation properly. For example, I have selected two random date formats and changed them according to the international format which includes the European(Germany) format and the US format.
📌 Steps:
- At first, select the column which you want to format.
- Then right-click on the data to select Format Cells.
- Next, select Number from the Format Cells box.
- After that, select Date from the Category and Location to German(Germany) to get a European format.
- Lastly, press OK to get the desired output format. Similarly, select the Location to English(United States) in order to get the US format.
How to Change Number Format in Excel to Millions
In this part, I will insert the ROUND Function to implement the number format in millions. The ROUND function rounds a number to a specified number of digits. For the purpose of demonstration, I have selected the following sample dataset.
📌 Steps:
- Firstly, select Cell C5 and write down the following formula.
=ROUND(B5/1000000,1)&" M"
- Secondly, hit the Enter key and use the AutoFill tool to the whole column.
- Finally, you will find the number in Millions as in the image below.
💬 Things to Remember
- In the first example, change Cell B5 according to your desired position of the value.
- The NUMBERVALUE function in the example doesn’t work in Excel 2010.
- Fortunately, you can choose any currency from the list.
- Also, you can change the number format to millions manually.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
Conclusion
These are all the steps you can follow to change the international number format in Excel. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
Related Articles
- How to Convert European Number Format to US in Excel
- [Solved] Excel Number Stored As Text
- How to Change Decimals to Percentages in Excel
- How to Convert Percentage to Decimal in Excel
- Excel Number Format Not Working
- [Solved:] Long Numbers Are Displayed Incorrectly in Excel
- How to Display Long Numbers in Excel
- How to Enter 16 Digit Number in Excel
- How to Enter 20 Digit Number in Excel
- How to Convert Exponential Value to Exact Number in Excel
- How to Format Number with VBA in Excel
