+ Reply to Thread
Results 1 to 14 of 14

Number formatting: General + separator?

  1. #1
    John Persons
    Guest

    Number formatting: General + separator?

    I would like to format cells so the numbers are displayed like the general format
    but with the thousands separator. I can't figure out how to insert the thousands
    separator without fixing the number of decimal places.

    For instance, I would like
    =1000*pi()
    to display as
    3,141.59265 (showing as many decimal places as will fit in the cell).

    I would like
    =10
    to display as
    10 .

    Thanks for any hints.

  2. #2
    Andrew Taylor
    Guest

    Re: Number formatting: General + separator?

    I don't think you can do this with a number format, but you can do it
    with a
    formula like:

    =TEXT(A1,"#,##0")&IF(A1=TRUNC(A1),"","."&RIGHT(A1-TRUNC(A1),LEN(A1-TRUNC(A1))-2))


    HTH
    Andrew Taylor


    John Persons wrote:
    > I would like to format cells so the numbers are displayed like the general format
    > but with the thousands separator. I can't figure out how to insert the thousands
    > separator without fixing the number of decimal places.
    >
    > For instance, I would like
    > =1000*pi()
    > to display as
    > 3,141.59265 (showing as many decimal places as will fit in the cell).
    >
    > I would like
    > =10
    > to display as
    > 10 .
    >
    > Thanks for any hints.



  3. #3
    Andrew Taylor
    Guest

    Re: Number formatting: General + separator?

    Actually it seems you _can_ (almost) do it, with number format
    #,##0.############### (15 #s after the decimal point, which will
    always be enough as Excel displays at most 15 digits precision).

    The only drawback is that whole numbers display with a trailing
    decimal point.

    Andrew



    Andrew Taylor wrote:
    > I don't think you can do this with a number format, but you can do it
    > with a
    > formula like:
    >
    > =TEXT(A1,"#,##0")&IF(A1=TRUNC(A1),"","."&RIGHT(A1-TRUNC(A1),LEN(A1-TRUNC(A1))-2))
    >
    >
    > HTH
    > Andrew Taylor
    >
    >
    > John Persons wrote:
    > > I would like to format cells so the numbers are displayed like the general format
    > > but with the thousands separator. I can't figure out how to insert the thousands
    > > separator without fixing the number of decimal places.
    > >
    > > For instance, I would like
    > > =1000*pi()
    > > to display as
    > > 3,141.59265 (showing as many decimal places as will fit in the cell).
    > >
    > > I would like
    > > =10
    > > to display as
    > > 10 .
    > >
    > > Thanks for any hints.



  4. #4
    Ron Rosenfeld
    Guest

    Re: Number formatting: General + separator?

    On 3 Nov 2005 14:50:57 GMT, John Persons <[email protected]> wrote:

    >I would like to format cells so the numbers are displayed like the general format
    >but with the thousands separator. I can't figure out how to insert the thousands
    >separator without fixing the number of decimal places.
    >
    >For instance, I would like
    > =1000*pi()
    >to display as
    > 3,141.59265 (showing as many decimal places as will fit in the cell).
    >
    >I would like
    > =10
    >to display as
    > 10 .
    >
    >Thanks for any hints.


    You will need to use a VBA event-triggered macro.

    Right click on the worksheet tab and select View Code.

    Paste the macro below into the window that opens.

    Change AOI appropriately to your ranges. That means make it cover as large an
    area as you might ever want to format this way. Subsequent coding will select
    only the cells with numbers to be formatted. This will both shorten the time
    for the routine to run, as well as ignore TEXT and errors within that range.

    ========================
    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim AOI As Range
    Dim Fmls As Range, Cnst As Range, Combined As Range
    Dim c As Range

    Set AOI = Range("A:A") 'set to range(s) you may wish to format this way

    'restrict AOI to just those cells containing numbers or formulas that --> a
    number
    'HELP says one can add the Type constants, but I could not get that to work.

    On Error GoTo Handler

    Set Fmls = AOI.SpecialCells(xlCellTypeFormulas, 1)
    Set Cnst = AOI.SpecialCells(xlCellTypeConstants, 1)

    If Fmls Is Nothing And Cnst Is Nothing Then Exit Sub

    If Fmls Is Nothing Then Set Combined = Cnst
    If Cnst Is Nothing Then Set Combined = Fmls
    If Combined Is Nothing Then Set Combined = Union(Fmls, Cnst)

    For Each c In Combined
    With c
    If Int(.Value) = .Value Then
    .NumberFormat = "#,##0"
    Else
    .NumberFormat = "#,##0.###############"
    End If
    End With
    Next c
    Exit Sub

    Handler:
    If Error = "No cells were found." Then
    Resume Next
    Else: MsgBox ("Error: " & Error)
    End If

    End Sub
    =====================================
    --ron

  5. #5
    John Persons
    Guest

    Re: Number formatting: General + separator?

    "Andrew Taylor" <[email protected]> wrote in
    news:[email protected]:

    > Actually it seems you _can_ (almost) do it, with number format
    > #,##0.############### (15 #s after the decimal point, which will
    > always be enough as Excel displays at most 15 digits precision).
    >
    > The only drawback is that whole numbers display with a trailing
    > decimal point.


    That has one other drawback besides the trailing decimal point. If the cell is
    only wide enough to show (say) 3 decimal points, it either widens the column or
    displays ###########.

    Thanks for the suggetion, though.

  6. #6
    Andrew Taylor
    Guest

    Re: Number formatting: General + separator?

    Replying to my own message yet again.... my suggested number
    format won't give the correct format if the number is very small: e.g.
    ..000000000000000000000001234

    Andrew


    Andrew Taylor wrote:
    > Actually it seems you _can_ (almost) do it, with number format
    > #,##0.############### (15 #s after the decimal point, which will
    > always be enough as Excel displays at most 15 digits precision).
    >
    > The only drawback is that whole numbers display with a trailing
    > decimal point.
    >
    > Andrew
    >
    >
    >
    > Andrew Taylor wrote:
    > > I don't think you can do this with a number format, but you can do it
    > > with a
    > > formula like:
    > >
    > > =TEXT(A1,"#,##0")&IF(A1=TRUNC(A1),"","."&RIGHT(A1-TRUNC(A1),LEN(A1-TRUNC(A1))-2))
    > >
    > >
    > > HTH
    > > Andrew Taylor
    > >
    > >
    > > John Persons wrote:
    > > > I would like to format cells so the numbers are displayed like the general format
    > > > but with the thousands separator. I can't figure out how to insert the thousands
    > > > separator without fixing the number of decimal places.
    > > >
    > > > For instance, I would like
    > > > =1000*pi()
    > > > to display as
    > > > 3,141.59265 (showing as many decimal places as will fit in the cell).
    > > >
    > > > I would like
    > > > =10
    > > > to display as
    > > > 10 .
    > > >
    > > > Thanks for any hints.



  7. #7
    John Persons
    Guest

    Re: Number formatting: General + separator?


    Thanks for the macro, Ron. I'm clearly naive about Excel -- I figured there *must*
    be a simple way to get the general formatting with comma separators!

    The drawback of the macro, for my purposes, is that Excel insists on widening the
    columns to show all the decimal places specified in the format code given in the
    macro. I would like it to behave like general formatting does by just showing the
    number of decimal places that will fit, given the width of the cell.

    For instance, I would like =1000*pi() to display 3,141.593 and =10000*pi() to
    display 31,415.93

  8. #8
    Ron Rosenfeld
    Guest

    Re: Number formatting: General + separator?

    On 4 Nov 2005 15:05:03 GMT, John Persons <[email protected]> wrote:

    >
    >Thanks for the macro, Ron. I'm clearly naive about Excel -- I figured there *must*
    >be a simple way to get the general formatting with comma separators!
    >
    >The drawback of the macro, for my purposes, is that Excel insists on widening the
    >columns to show all the decimal places specified in the format code given in the
    >macro. I would like it to behave like general formatting does by just showing the
    >number of decimal places that will fit, given the width of the cell.
    >
    >For instance, I would like =1000*pi() to display 3,141.593 and =10000*pi() to
    >display 31,415.93


    That specification is a bit different from your original specification which
    was:

    For instance, I would like
    =1000*pi()
    to display as
    3,141.59265 (showing as many decimal places as will fit in the cell).

    Do you want to leave the width of the cell fixed, at whatever you happen to set
    it prior to setting the format?

    If so, what should happen if the cell width is too small to display the number,
    which could happen if the number is a large integer.

    What do you want to happen if the cell width is not enough to display ANY of
    the fractional portion?

    In any event, this modification will adjust the formatting of fractional
    numbers so as to fit in the current column width. If your number is
    fractional, but the column width is too narrow to display ANY of the fractional
    portion, it will be displayed with a terminal decimal point.

    In other words, =10000*PI() might display as 31,416. if there is not enough
    room to display ANY of the decimals.

    This is very preliminary, and will work only for your NORMAL font, but can be
    modified once you supply more precise specifications.

    =================================
    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim AOI As Range
    Dim Fmls As Range, Cnst As Range, Combined As Range
    Dim c As Range
    Dim W
    Set AOI = Range("A:A") 'set to range(s) you may wish to format this way

    'restrict AOI to just those cells containing numbers _
    or formulas that --> a Number
    'HELP says one can add the Type constants, but I _
    could not get that to work.

    On Error GoTo Handler

    Set Fmls = AOI.SpecialCells(xlCellTypeFormulas, 1)
    Set Cnst = AOI.SpecialCells(xlCellTypeConstants, 1)

    If Fmls Is Nothing And Cnst Is Nothing Then Exit Sub

    If Fmls Is Nothing Then Set Combined = Cnst
    If Cnst Is Nothing Then Set Combined = Fmls
    If Combined Is Nothing Then Set Combined = Union(Fmls, Cnst)

    For Each c In Combined
    With c
    W = .ColumnWidth
    If Int(.Value) = .Value Then
    .NumberFormat = "#,##0"
    Else
    .NumberFormat = "#,##0." & Application.WorksheetFunction.Rept _
    ("#", Application.WorksheetFunction.Max(0, W - Len(Format _
    (Int(.Value), "#,##0."))))
    End If
    End With
    Next c
    Exit Sub

    Handler:
    If Error = "No cells were found." Then
    Resume Next
    Else: MsgBox ("Error: " & Error)
    End If

    End Sub
    ==============================
    --ron

  9. #9
    Andrew Taylor
    Guest

    Re: Number formatting: General + separator?

    Ah, sorry, I misunderstood the question. Still, I taught myself
    something I didn't know about number formats..


    John Persons wrote:
    > "Andrew Taylor" <[email protected]> wrote in
    > news:[email protected]:
    >
    > > Actually it seems you _can_ (almost) do it, with number format
    > > #,##0.############### (15 #s after the decimal point, which will
    > > always be enough as Excel displays at most 15 digits precision).
    > >
    > > The only drawback is that whole numbers display with a trailing
    > > decimal point.

    >
    > That has one other drawback besides the trailing decimal point. If the cell is
    > only wide enough to show (say) 3 decimal points, it either widens the column or
    > displays ###########.
    >
    > Thanks for the suggetion, though.



  10. #10
    John Persons
    Guest

    Re: Number formatting: General + separator?

    Ron Rosenfeld <[email protected]> wrote in
    news:[email protected]:

    > On 4 Nov 2005 15:05:03 GMT, John Persons <[email protected]>
    > wrote:
    >
    >>
    >>Thanks for the macro, Ron. I'm clearly naive about Excel -- I figured
    >>there *must* be a simple way to get the general formatting with comma
    >>separators!
    >>
    >>The drawback of the macro, for my purposes, is that Excel insists on
    >>widening the columns to show all the decimal places specified in the
    >>format code given in the macro. I would like it to behave like
    >>general formatting does by just showing the number of decimal places
    >>that will fit, given the width of the cell.
    >>
    >>For instance, I would like =1000*pi() to display 3,141.593 and
    >>=10000*pi() to display 31,415.93

    >
    > That specification is a bit different from your original
    > specification which was:
    >
    > For instance, I would like
    > =1000*pi()
    > to display as
    > 3,141.59265 (showing as many decimal places as will fit in the
    > cell).
    >
    > Do you want to leave the width of the cell fixed, at whatever you
    > happen to set it prior to setting the format?
    >
    > If so, what should happen if the cell width is too small to display
    > the number, which could happen if the number is a large integer.
    >
    > What do you want to happen if the cell width is not enough to display
    > ANY of the fractional portion?


    I would like it to behave something like the general format seems to behave:

    (a) If the number is very close to zero (so with the existing cell width, the
    display would be all zeroes except for perhaps the last two digits), then use
    scientific notation. Widening the column instead of using scientific notation
    would also be fine.

    (b) If abs(x) is so large that the integer portion will not fit in the existing
    cell width, then use scientific notation. Widening the column instead of using
    scientific notation would also be fine.

    (c) Else, show as many decimal places as will fit in the cell.


    > In any event, this modification will adjust the formatting of
    > fractional numbers so as to fit in the current column width. If your
    > number is fractional, but the column width is too narrow to display
    > ANY of the fractional portion, it will be displayed with a terminal
    > decimal point.


    Slick! It doesn't handle category (a) above the way I would like. I could
    change those cells manually to scientific notation, but the formatting changes
    back when I edit any cell in AOI. Is there a way to get the macro to skip over
    cells that are currently formatted to something other than general or the two
    formats prescribed by the macro? That way particular cells within AOI could have
    different formats.

    Thanks again from a VBA illiterate!

  11. #11
    Ron Rosenfeld
    Guest

    Re: Number formatting: General + separator?

    On 4 Nov 2005 20:14:04 GMT, John Persons <[email protected]> wrote:

    >Ron Rosenfeld <[email protected]> wrote in
    >news:[email protected]:
    >
    >> On 4 Nov 2005 15:05:03 GMT, John Persons <[email protected]>
    >> wrote:
    >>
    >>>
    >>>Thanks for the macro, Ron. I'm clearly naive about Excel -- I figured
    >>>there *must* be a simple way to get the general formatting with comma
    >>>separators!
    >>>
    >>>The drawback of the macro, for my purposes, is that Excel insists on
    >>>widening the columns to show all the decimal places specified in the
    >>>format code given in the macro. I would like it to behave like
    >>>general formatting does by just showing the number of decimal places
    >>>that will fit, given the width of the cell.
    >>>
    >>>For instance, I would like =1000*pi() to display 3,141.593 and
    >>>=10000*pi() to display 31,415.93

    >>
    >> That specification is a bit different from your original
    >> specification which was:
    >>
    >> For instance, I would like
    >> =1000*pi()
    >> to display as
    >> 3,141.59265 (showing as many decimal places as will fit in the
    >> cell).
    >>
    >> Do you want to leave the width of the cell fixed, at whatever you
    >> happen to set it prior to setting the format?
    >>
    >> If so, what should happen if the cell width is too small to display
    >> the number, which could happen if the number is a large integer.
    >>
    >> What do you want to happen if the cell width is not enough to display
    >> ANY of the fractional portion?

    >
    >I would like it to behave something like the general format seems to behave:
    >
    >(a) If the number is very close to zero (so with the existing cell width, the
    >display would be all zeroes except for perhaps the last two digits), then use
    >scientific notation. Widening the column instead of using scientific notation
    >would also be fine.
    >
    >(b) If abs(x) is so large that the integer portion will not fit in the existing
    >cell width, then use scientific notation. Widening the column instead of using
    >scientific notation would also be fine.
    >
    >(c) Else, show as many decimal places as will fit in the cell.
    >
    >
    >> In any event, this modification will adjust the formatting of
    >> fractional numbers so as to fit in the current column width. If your
    >> number is fractional, but the column width is too narrow to display
    >> ANY of the fractional portion, it will be displayed with a terminal
    >> decimal point.

    >
    >Slick! It doesn't handle category (a) above the way I would like. I could
    >change those cells manually to scientific notation, but the formatting changes
    >back when I edit any cell in AOI. Is there a way to get the macro to skip over
    >cells that are currently formatted to something other than general or the two
    >formats prescribed by the macro? That way particular cells within AOI could have
    >different formats.
    >
    >Thanks again from a VBA illiterate!


    Try this. I've not done extensive testing, but I think it will work pretty
    close to what you want, and might even properly handle different sized fonts.

    ================================
    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim AOI As Range
    Dim Fmls As Range, Cnst As Range, Combined As Range
    Dim c As Range
    Dim W, SFZ

    SFZ = Application.StandardFontSize
    Set AOI = Range("A:A") 'set to range(s) you may wish to format this way

    'restrict AOI to just those cells containing numbers _
    or formulas that --> a Number
    'HELP says one can add the Type constants, but I _
    could not get that to work.

    On Error GoTo Handler

    Set Fmls = AOI.SpecialCells(xlCellTypeFormulas, 1)
    Set Cnst = AOI.SpecialCells(xlCellTypeConstants, 1)

    If Fmls Is Nothing And Cnst Is Nothing Then Exit Sub

    If Fmls Is Nothing Then Set Combined = Cnst
    If Cnst Is Nothing Then Set Combined = Fmls
    If Combined Is Nothing Then Set Combined = Union(Fmls, Cnst)

    For Each c In Combined
    With c
    W = .ColumnWidth * SFZ / .Font.Size
    If Int(.Value) = .Value Then
    .NumberFormat = "#,##0"
    Else
    .NumberFormat = "#,##0." & Application.WorksheetFunction.Rept _
    ("#", Application.WorksheetFunction.Max(0, W - Len(Format _
    (Int(.Value), "#,##0."))))
    End If

    'Check for proper display
    If Right(.Text, 1) = "." Then .NumberFormat = "#,##0"
    If .Text = 0 Or Left(.Text, 1) = "#" Then .NumberFormat = "General"
    End With
    Next c
    Exit Sub

    Handler:
    If Error = "No cells were found." Then
    Resume Next
    Else: MsgBox ("Error: " & Error)
    End If

    End Sub
    ===========================


    --ron

  12. #12
    John Persons
    Guest

    Re: Number formatting: General + separator?

    Ron Rosenfeld <[email protected]> wrote in
    news:[email protected]:
    >
    > Try this. I've not done extensive testing, but I think it will work
    > pretty close to what you want, and might even properly handle
    > different sized fonts.


    Thanks again, Ron! I have been messing around with it and it seems to work well.
    Is there an easy method that would allow me to set particular cells in the range to
    a different format? That way I could use this as the default format for the
    worksheet, but some cells could be formatted differently.

    I suppose I could define AOI to be multiple ranges by editing the VBA code whenever
    I need to format a cell differently, but that would be a pain. If there is a
    clever way to make the macro skip over any cells in AOI that have been formatted
    differently, that would be great.

  13. #13
    Ron Rosenfeld
    Guest

    Re: Number formatting: General + separator?

    On 7 Nov 2005 20:05:45 GMT, John Persons <[email protected]> wrote:

    >Ron Rosenfeld <[email protected]> wrote in
    >news:[email protected]:
    >>
    >> Try this. I've not done extensive testing, but I think it will work
    >> pretty close to what you want, and might even properly handle
    >> different sized fonts.

    >
    >Thanks again, Ron! I have been messing around with it and it seems to work well.
    >Is there an easy method that would allow me to set particular cells in the range to
    >a different format? That way I could use this as the default format for the
    >worksheet, but some cells could be formatted differently.
    >
    >I suppose I could define AOI to be multiple ranges by editing the VBA code whenever
    >I need to format a cell differently, but that would be a pain. If there is a
    >clever way to make the macro skip over any cells in AOI that have been formatted
    >differently, that would be great.


    Well, you'd have to know in advance what format to check for, and if it's
    already in the cell, don't reformat it. Or some other common characteristic of
    the cells to be formatted (or not formatted).

    You'd also have to be sure that your "excluded formats" don't include any of
    these custom formats.

    Another approach would be to select the cells you wish to format (or not
    format) and include(exclude) those cells that are selected.

    Think about what sort of approach you want to take.


    --ron

  14. #14
    Registered User
    Join Date
    08-26-2014
    Location
    Iran,tehran
    MS-Off Ver
    2010
    Posts
    10

    Re: Number formatting: General + separator?

    hello everybody
    I want to use , and . at the same time. for example I want to type 12125.65 in a textbox in form and see this as 12,125.65
    when I type this code it idoesn't work :
    Please Login or Register  to view this content.
    please help me

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1