Custom number format

[Michael Bailey]

I use Excel 2013 and I regularly create spreadsheets in Latin American Spanish. I have the Spanish Language Pack installed but it does nothing with number formats.

In Latin American countries they use a comma instead of a period to indicate a decimal point. They use periods instead of commas as a thousands separator and an apostrophe as a million or greater separator. I have tried creating a custom number format but simply replacing the period with a comma doesn't work because apparently the comma indicates additional functions. I can find no way to use an apostrophe as a millions, billions or trillions separator. I've given some examples of what I need below.

US format                            Latin American format

110,500                                110.500

12,000.00                             12.000,00

3,450,000                             3'456.000

4,322,000,000.00                 4'322.000.000,00

How do I create custom number formats for this type of display?

[#2]

Hi Michael,

in Options / Advanced you can set which signs are used for separators. There you can quite easily exchange the , and the .

But its an ExcelSetting and not saved with the Excel-file. Not sure if that already helps you. I have also tried the apostrophe seperator, but even if you set up a user-defined format it still uses the , from the thousands additionally, so you get 1,'000,000.00

Jens

[#3]

The decimal point and the thousand separator are linked to the regional settings of your computer. If you format a number with the thousand separator, it will appear according to your regional settings. If you send that same workbook to somebody with different regional settings, they will see THEIR respective thousand separator, according to the regional settings of the computer.

So, Excel is quite flexible with the formatting. If you want to switch to different regional settings, you can change the Windows settings to use a different language in the Control Panel. In the screenshot below, you can select another language from the dropdown for the display language.

The key here is that you don't need to use formatting to have numbers displayed according to the custom of a country. Windows and Excel will take care of that, and you can select what settings you want to apply.