Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Formatting Currency.
Written by Allen Wyatt (last updated October 19, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003
Eric asked if there was a way to easily format cells so what would normally appear as $10,000.00 would appear as $10.000,00. This format being described is the difference between the US method of displaying figures (using commas as thousand separators and a period as a decimal sign) and the European method of displaying figures (using periods as thousand separators and a comma as a decimal sign).
There are three ways you can accomplish a switch. The easiest method is to simply change the Regional Settings in Windows. The exact way you do this depends on the version of Windows you are using, but in general there is a choice in the Windows Control Panel that allows you to specify regional settings. All you need to do is modify those settings to match the numeric display format desired. The change will affect not only the display of numbers in Excel, but in other Windows-compliant programs, as well.
The second method is to use a formula to handle the numeric display. This has the drawback of converting the numeric value to text, but it could be easily done. For instance, let's assume that you have the formatted numeric value $10,000.00 in cell A1. The following formula, in a different cell, would display the text $10.000,00:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(A1, "$#,##0.00"),".","^"),",","."),"^",",")
This formula first converts the number to an initial currency format in text. Then the SUBSTITUTE function is used to first change "." to "^" ("^" is used as a temporary placeholder), and then change "," to ".", and finally "^" to ",".
The final method has the advantage of leaving your numbers as numbers, instead relying on a custom format. All you need to do is to multiply your values by 100 and then use the following custom format:
#"."###"."###","##
The format allows any number up to 9.999.999,00 to be used. If you deal with numbers that have more than two decimal places, you will need to adjust your custom format accordingly, or adjust the value being displayed so that it has nothing to the right of the decimal point after it is multiplied by 100.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (1935) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Formatting Currency.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
Need to cram a bunch of text all on a single line in a cell? You can do it with one of the lesser-known settings in Excel.
Discover MoreWant to change the size of the font within a worksheet? Excel allows you to choose from a list of sizes, as well as ...
Discover MoreCells in a worksheet defined by the intersection of rows and columns. If you adjust row height and column width just ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-07-05 05:35:58
Hi -
Just to clarify - it's continental Europe, bits of Africa and South America and who use " . " to group thousands and " , " to as a decimal sign.
US, UK, India, China use the "," to group thousands.
MS handle this in the operating system - so if you send a file from the UK to Germany, the recipient will see numbers formatted in way they recognise.
This can, however, cause problems in formula that reference text strings or reports that you want to export.... So thanks for the tip.
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here:
Comments