How to Change International Number Format in Excel (4 Examples)

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.


Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.


4 Suitable Examples to Change International Number Format in Excel

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 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.

Sample Dataset for How to Change International Number Format in Excel


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,",",".")

How to Change International Number Format in Excel by Using NUMBERVALUE Function

  • 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.

Read More: How to Custom Number Format in Excel with Multiple Conditions


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),".","$"),",","."),"$",",")

Using SUBSTITUTE and FIXED Function to Change International Number Format in Excel

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 Number Format from Comma to Dot in Excel (5 Ways)


Similar Readings


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.

Sample Dataset to Change International Currency Format in Excel

๐Ÿ“Œย  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.

Format Cells Dialog Box to Change International Number Format in Excel

  • Finally, you will get your desired currency as output. Similarly, I have got the output for US Dollar.

Read More: How to Add Currency Symbol in Excel (6 Ways)


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.

Dataset to International Date Format in Excel

๐Ÿ“Œย  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.

Read More: How to Use Number Format Code in Excel (13 Ways)


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.

Dataset to Change Number Format to Millions

๐Ÿ“Œย  Steps:

  • Firstly, select Cell C5 and write down the following formula.

=ROUND(B5/1000000,1)&" M"

How to Change Number Format in Excel to Millions by Using ROUND Function

  • 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.

Read More: How to Format a Number in Thousands K and Millions M in Excel (4 Ways)


๐Ÿ’ฌ 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.

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.

For more information like this, visit Exceldemy.com.


Related Articles

Mehedi Hasan

Mehedi Hasan

Hi, I am Mehedi. I have completed my B.Sc. from Bangladesh University of Engineering and Technology. I have a strong interest in innovation and research in the field of Data Science and Machine Learning. Gradually, I now understand the value of Data Analysis and I am trying to learn everyday.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo