Results 1 to 7 of 7

Thread: Custom Format

  1. #1
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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-cu...er-formats.htm) but cannot seem to pull it off.

    I would appreciate any clues.
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='770472' date='14-Apr-2009 14:07']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.[/quote]

    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!
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Attached Images Attached Images

  5. #5
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  7. #7
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello Hans,

    Thank you! This is two steps closer to "the ideal fix" . I didn't intend that you bother this much!
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •