Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2008
    Posts
    99
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Display negative numbers as (xxx) in a table

    I have a table in Word, already entered. Now the boss wants all the negative numbers to be displayed with parentheses, so
    -3,210
    would be displayed as
    (3,210)

    Is there an easy way of doing this?

    Oh, yes: While at the moment I don't have to sort this, I would not be surprised if that is the next request. So, the number needs to stay as a number, at least for sorting purposes.
    [I have been here for years; I had to get things restarted]

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Willow Grove, Pennsylvania, USA
    Posts
    205
    Thanks
    4
    Thanked 49 Times in 40 Posts
    This kind of thing would be a huge PITA in Word, even without the need to sort. But it's exactly the kind of thing Excel does routinely. So...

    Copy the table and paste it into Excel.
    Select the numeric column(s), press Ctrl+1, and select the Numeric type; then select the option to show negative numbers with parentheses.
    Copy the data in Excel, then switch back to Word.
    Delete the existing table. With the cursor in its place, click the down arrow on the Paste button and choose Paste Special.
    Click the Paste Link option. Choose a format (probably HTML Format or RTF will work equally well) and click OK. The result is a table that's linked to the Excel worksheet.

    If you need to change the numbers, do it in the Excel sheet. The results will appear in the Word document automatically.
    Format the table as needed.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    It's actually easy to do in Word if you're using a formula in the table. From the formula's number format options, use the one shown in this image.

    Clip0003.jpg

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    This can be done quite easily with a macro:
    Code:
    Sub Demo()
    Application.ScreenUpdating = False
    With ActiveDocument.Range
      With .Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Text = "-[$€ге0-9,.]{1,}"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindStop
        .Format = False
        .MatchWildcards = True
        .Execute
      End With
      Do While .Find.Found
        If .Information(wdWithInTable) = True Then
          .Characters.First = "("
          .InsertAfter ")"
        End If
        .Collapse wdCollapseEnd
        .Find.Execute
      Loop
    End With
    Application.ScreenUpdating = True
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Canada
    Posts
    122
    Thanks
    3
    Thanked 20 Times in 18 Posts
    You don't even need to use a macro to do this; a single Find and Replace can do it in one step:

    In the Find what box: (-)([$€ге0-9,.]{1,})
    In the Replace with box: (\2)
    Turn on "Use wildcards"

    The pattern looks for a minus sign followed by a string consisting of an number of digits plus comma, period and common currency forms (good thinking Paul!). When found, it replaces it with the 2nd pattern surrounded by parentheses.

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by EricFletcher View Post
    You don't even need to use a macro to do this
    That's only OK if you don't care about the Find/Replace changing content outside the table... otherwise, you either have to select each table and use ReplaceAll on the selected range only, or not use ReplaceAll and have to go through every instance individually.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Canada
    Posts
    122
    Thanks
    3
    Thanked 20 Times in 18 Posts
    Since the OP only mentioned "a" table in his document, and given that many users may lack confidence or experience with macros & VBA, I thought I should point out that the F&R dialog can manage it without a macro.

    Macropod's solution is certainly more elegant in that it will work for any number of tables -- and his code is a good example of how one could implement other similar changes in multiple tables.

Posting Permissions

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