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

Example 1 – Converting European Number Format to US Number Format

  Steps:

  • Select the Cell C5 and enter the following formula.

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

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

  • Press the Enter key and use the AutoFill tool to apply the formula to the rest of the cells.

  • Select the entire column, right-click and select Format Cells.

  • From the Format Cells dialog box, select Number from the Category list and check Use 1000 Separator().
  • Click OK.

  • You will get your desired result.

Read More: How to Change Decimals to Percentages in Excel


Example 2 – Converting US Number Format to European Number Format

  Steps:

  • Select Cell C5 and enter the following formula.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FIXED(B5,2,FALSE),".","$"),",","."),"$",",")

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

Formula Breakdown

  • FIXED(B5,2,FALSE) takes the value from B5 and returns the value up to two decimals 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.
  • (SUBSTITUTE(FIXED(B5,2,FALSE),”.”,”$”),”,”,”.”) function takes the output and returns after replacing every comma with a dot.
  • SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FIXED(B5,2,FALSE),”.”,”$”),”,”,”.”),”$”,”,”) function replaces every $ sign with a comma and returns the final output.
  • Press Enter and use the AutoFill tool.

  • The converted data will be as shown in the following image.

Read More: How to Change Comma to Dot in Excel


Example 3 – Changing International Currency Format

Sample Dataset to Change International Currency Format in Excel

  Steps:

  • Select the entire column, right-click and select Format Cells.

  • In the Format Cells dialog box, select Currency from the Category options and select Euro from the Symbol options.

Format Cells Dialog Box to Change International Number Format in Excel

  • You will get your desired currency as output.


Example 4 – Changing International Date Format in Excel

Dataset to International Date Format in Excel

  Steps:

  • Select the column you want to format.
  • Right-click on the data, select Format Cells.

  • Select Number from the Format Cells box.
  • Select Date from the Category and Location to German(Germany) to get a European format.

  • Select the Location to English(United States) in order to get the US format and click OK.


How to Change Number Format in Excel to Millions

The ROUND function rounds a number to a specified number of digits. Dataset to Change Number Format to Millions

  Steps:

  • Select Cell C5 and enter the following formula.

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

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

  • Press Enter and use the AutoFill tool for the remaining cells.

  • You will get the number in Millions as shown in the image below.


Download Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hasan
Mehedi Hasan

Mehedi Hasan, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a profound passion for research and innovation, he actively engages with Excel. In his capacity, Mehedi not only adeptly tackles intricate challenges but also showcases enthusiasm and expertise in navigating tough situations with finesse, underscoring his unwavering dedication to consistently delivering exceptional and high-quality content. He... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo