Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Oct 2018
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Number Formatting - MS Word

    Hi,

    I want to convert the figure's format in Word document from "General" to " Accounting". For ex. 100000 to 100,000.
    Please let me know, is there any easy way to do that ?

    Regards,
    Eklaveya Dubey

  2. #2
    Silver Lounger kweaver's Avatar
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,706
    Thanks
    60
    Thanked 89 Times in 82 Posts
    Word, unfortunately, doesn't offer a simple way to format numbers like Excel.

    You could link an Excel sheet to your Word document to more easily accomplish this.

    Alternatively (but not particularly easy) is to insert a table in the Word document. In a simplistic case, you might put numbers in the first row.
    Then, position the mouse and insertion point in the second row, 1st cell, and from the keyboard enter: CTRL+F9 which will create field braces.
    Inside the braces enter: =A1 \# "#,###" which says to Word to use the first row, first column (like Excel) and format it with a comma in thousands.
    Copy that formula to the "B column" etc. but change the cell pointer to B1, C1, etc. Now enter values in the first row, select the table, and update the table using the F9 key. [pain] Then, ALT+F9 will close all the fields just entered.

    I've done this when I really was forced to stay within Word. You could remove the borders and change the font color in the first row to white (to match the page).

    I'd really suggest using a link to Excel, however.
    Last edited by kweaver; 2018-10-11 at 13:33.

  3. #3
    WS Lounge VIP Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    1,445
    Thanks
    318
    Thanked 245 Times in 209 Posts
    Hi Eklaveya, welcome to WS Lounge ☺

    What version of Word are you using?

    What are you trying to achieve that simply typing…
    100,000
    …won't accomplish?
    Lugh.
    ~
    Dell Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
    i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 2 x 256G SSD, 4TB HD

  4. #4
    New Lounger
    Join Date
    Oct 2018
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, Kweaver.
    But I am having large volume of data to be converted. So I think i have to apply some VBA codes for that which I am trying to get.
    But thanks for your valuable inputs.

  5. #5
    New Lounger
    Join Date
    Oct 2018
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Lugh, but I am having large volume of data, which I think could be possible by VBA codes & I am trying to get that..

  6. #6
    New Lounger
    Join Date
    Oct 2018
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Or someone is aware, how to use FIND & REPLACE option by turning on "Use wildcards"..?

  7. #7
    WS Lounge VIP Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    1,445
    Thanks
    318
    Thanked 245 Times in 209 Posts
    Quote Originally Posted by EKLAVEYA View Post
    Or someone is aware, how to use FIND & REPLACE option by turning on "Use wildcards"..?
    There is a Find/Replace code…
    ^#
    …which means 'any digit', but I don't see how you could use it easily—problem is, there isn't an 'any non-digit' code which you need to use with ^#.

    My guess is you'll need to use a regular expression, specifically the square bracket option [ ]

    Check out Graham Mayor's page on it, Graham is one of the regular Word expert posters here.
    Lugh.
    ~
    Dell Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
    i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 2 x 256G SSD, 4TB HD

  8. #8
    New Lounger
    Join Date
    Oct 2018
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Lugh.
    I got the VBA Codes for converting the digits from general format to accounting format but I have to select the digits column page by page individually. Is there any option to change these codes to run for the whole document ? Codes are:

    Sub NumberFormat()
    Dim oCel As Cell, RngCel As Range
    With Selection
    If .Information(wdWithInTable) = True Then
    For Each oCel In .Cells
    Set RngCel = oCel.Range
    RngCel.MoveEnd Unit:=wdCharacter, Count:=-1
    If IsNumeric(RngCel.Text) Then
    If InStr(RngCel.Text, ",") = 0 Then
    If InStr(RngCel.Text, ".") = 0 Then
    RngCel.Text = Format(RngCel.Text, "#,##0")
    Else
    RngCel.Text = Format(RngCel.Text, "#,##0.00")
    End If
    End If
    End If
    Next
    Set RngCel = Nothing
    End If
    End With
    End Sub

  9. #9
    2 Star Lounger Graham Mayor's Avatar
    Join Date
    Mar 2016
    Location
    Cyprus
    Posts
    112
    Thanks
    0
    Thanked 40 Times in 38 Posts
    How about

    Code:
    Sub FormatNumbers()
    Dim orng As Range
        Set orng = ActiveDocument.Range
        With orng.Find
            Do While .Execute(FindText:="[0-9]{4,}", MatchWildcards:=True)
                orng.Text = Format(orng.Text, "###,###,##0")
                orng.Collapse 0
            Loop
        End With
    lbl_Exit:
        Set orng = Nothing
        Exit Sub
    End Sub
    Graham Mayor - Word MVP
    http://www.gmayor.com

  10. #10
    New Lounger
    Join Date
    Oct 2018
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    THANKS Graham, u are really awesome.
    It's of great help.

  11. #11
    New Lounger
    Join Date
    Oct 2018
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks to other's also, as they have taken initiative to sort out my problem.
    Thanks Guys.

  12. #12
    New Lounger
    Join Date
    Nov 2018
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    To follow up on this, is there a way to code it so that the formatting will also apply to content control items?

  13. #13
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    978
    Thanks
    4
    Thanked 15 Times in 14 Posts
    Yes, you can use the following code to format content controls. (Only content controls with numbers -- no text at all -- will be formatted.

    Code:
    Sub ContentControlNumFormat()
        Dim cc As ContentControl
        For Each cc In ActiveDocument.ContentControls
            If IsNumeric(cc.Range.Text) Then
                cc.Range.Text = Format(cc.Range.Text, "###,###,##0")
            End If
        Next
    End Sub

  14. #14
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    978
    Thanks
    4
    Thanked 15 Times in 14 Posts
    Yes, you can use the following code to format content controls. (Only content controls with numbers -- no text at all -- will be formatted.

    Code:
    Sub ContentControlNumFormat()
        Dim cc As ContentControl
        For Each cc In ActiveDocument.ContentControls
            If IsNumeric(cc.Range.Text) Then
                cc.Range.Text = Format(cc.Range.Text, "###,###,##0")
            End If
        Next
    End Sub
    Or, you can do this each time you exit a content control, by putting the following code in the Document portion of the VBA project:

    Code:
    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
        If IsNumeric(ContentControl.Range.Text) Then
           ContentControl.Range.Text = Format(ContentControl.Range.Text, "###,###,##0")
        End If
    End Sub
    Last edited by richardbarrett; 2018-11-05 at 17:16.

  15. #15
    New Lounger
    Join Date
    Nov 2018
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That's a lot simpler than what I ended up piecing together -

    Private Sub Document_Open()
    Dim RngCel As Range, oCel As Cell, oTbl As Table
    For Each oTbl In ActiveDocument.Tables
    For Each oCel In oTbl.Columns(2).Cells
    Set RngCel = oCel.Range
    RngCel.End = RngCel.End - 1
    If IsNumeric(RngCel.Text) Then
    If InStr(RngCel.Text, ",") = 0 Then
    If InStr(RngCel.Text, ".") = 0 Then
    RngCel.Text = Format(RngCel.Text, "#,##0")
    Else
    RngCel.Text = Format(RngCel.Text, "#,##0.00")
    End If
    End If
    End If
    Next oCel
    Next oTbl
    End Sub

    BUT ... while it works perfectly, it won't work in my application. I'm unable to save in .docm and upload to where it needs to go.

    So, my third idea was to have the content control field in a table and have a cell beside the table reference it and apply formatting. Which works just fine... when creating the formula in the document. It doesn't automatically apply the formula upon opening the document, if it's saved as a template.

    Word is KILLING me. Any ideas would be most appreciated. Basically here's my problem -

    A content control doesn't have formatting for the number field, so it drops the 0 after a decimal. For example: 17.45 keeps it's 2-places-after-decimal format. But 17.40 will drop the 0, appearing as 17.4 in the control. I'm trying to force the control to remain ##0.00 formatting. (Interestingly enough, just entering 8 in the application will have the control in Word appear as 8.00).

Tags for this Thread

Posting Permissions

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