Custom Format

Topic

New Reply

Using Excel 2003, WinXP. My regional settings are set so numbers show like 3.499.123,28. The custom format is “#.##0,00”

I would like a custom format so they will show like 3,499,123.28. I know I can change the regional settings, even go to Tools | Options within Excel, but I would rather apply a custom format if possible. I’ve been playing around a little bit (http://www.ozgrid.com/Excel/excel-custom-number-formats.htm) but cannot seem to pull it off.

I would appreciate any clues.

Reply | Quote

Replies

I don’t think that’s possible. Custom number formats use the decimal and thousands separators set in Windows or in the International tab of Tools | Options…

If you send your workbook to someone else, Excel will use the decimal and thousands separators from their system, so there’s no real need to display numbers differently.

Reply | Quote

If you send your workbook to someone else, Excel will use the decimal and thousands separators from their system, so there’s no real need to display numbers differently.

Hello Hans,

In fact there is. I have an Excel spreadsheet supporting a Word report which is intended for US users. My local settings display numbers as 3.499.123,28. These are the default settings in Argentina and I don’t want to change them. However, I need to paste tables from Excel to Word in US-compliant format. That’s why I would like this custom format, so I can apply it whenever a similar need arises without having to go switching regional settings.

Thank you anyway!

Reply | Quote

Hi Diego:

You can modify this characteristic within the options of excel, this will affect all the books that you have, but only in Excel and not Access or any other program.
I am going to write the procedure in spanish because I suppose you have that versión.

Procedimiento:
1. Menu Herramientas | Opciones
2. Tab o Pestaña Internacional
3. Desactiva la casilla de verificación “usar separadores del sistema”
– Cambia el caracter de separador decimal y el separador de miles como se muestra en la imagen.
4. Presiona el boton “Aceptar”

I am going to try in English.
Procedure:
1. Menu Tools | options
2. Tab International
3. Deactivate the checkbox “to use separators of the system”
– Change “Decimal separator” and “thousands separator”.
4. click in the button to “OK”.

Sorry if my version in English is not so good, but somebody can help me to correct it, I will appreciate any help.

Reply | Quote

Hello Servando,

Thanks for your customized response

For your information I am using an English version of Excel at work, and I could follow your directions right away.

This is exactly what I’ll do, but it would be more useful if the changes were local to the book rather than global (just as the Manual Save setting). It is inconvenient to change global settings each time I need to copy and paste into word a statement with some figures to report to the US or any other country with a similar system.

Fortunately this doesn’t happen too often, but I was looking for the ideal fix.

Thank you very much.

Reply | Quote

You could create two macros in your Personal.xls workbook:

Code:

Sub USFormat()
  ' Force the use of US number format
  With Application
	.DecimalSeparator = "."
	.ThousandsSeparator = ","
	.UseSystemSeparators = False
  End With
End Sub

Sub SystemFormat()
  ' Restore system number format
  Application.UseSystemSeparators = True
End Sub

and assign them to custom toolbar buttons and/or keyboard shortcuts.

You can then run the first macro, copy and paste some data into Word, then run the second macro.

Reply | Quote

Hello Hans,

Thank you! This is two steps closer to “the ideal fix” . I didn’t intend that you bother this much!

Reply | Quote