Modifying the way Number Display

Microsoft Word 2000
December 3, 2009 at 22:34:05
Specs: Windows XP, 1GB
I want to display my numbers in Word 2000 formula result in a different way:

Example: 10,00,00,000.00
that is the commas should come after every 2 digits in the thousand place and not the way word automatically shows it as 100,000,000.00


See More: Modifying the way Number Display

Report •


#1
December 4, 2009 at 05:35:09
Hi,

I don't think that you can create a format that adds a ',' within a number in places other than every 3 characters (thousands).

If you are bringing the data in from Excel, you could manipulate the data in Excel, creating a series of cells containing text, such as

10,
00,
000
.00
and then import those cells as text not numbers.

In Excel

		A	B	C	D	E
1	9123456.78	91,	23,	456	.78
The original number is in cell A1
B1 contains =LEFT(TEXT(A1,"0.00"),2)&","
C1 contains =MID(TEXT($A$1,"0.00"),3,2)&","
and so on.
Copy each cell B1 to E1, and paste special for each cell into Word, using Paste Link and Unformatted text
This resulted in
91,23,456.78
in Word.
(You can't select all the cells B1 to E1 and paste special / Paste Link / Unformatted text as you get Tabs between each cell's data)

Regards


Report •

#2
December 4, 2009 at 11:14:14
Try personalized formatting ##,##,##,##,#0.00

Nigel

Mobo: Asus P7P55D LE
OS: Microsoft Windows 7 Professional OEM
CPU: Core i5 750 @ 2.67 GHz
RAM: Corsair Dominator DHX+ DDR3 1600MH
GPU: Sapphire 4870 DDR5


Report •

#3
December 4, 2009 at 17:02:52
Hi Nigel Spike,

Can you show the actual format in a Word field, as I couldn't get a custom format to work.

Thanks


Report •

Related Solutions

#4
December 4, 2009 at 18:34:21
Isn't there something like Digit Grouping, like there is in Windows control panel, where we can modify how we want the date, numbers, currency to be displayed.

There should be something where it can be modified...


Report •

#5
December 5, 2009 at 14:37:40
Hi,

I am not aware of another option for formatting numbers in Word.

Custom formating with "#,#0.00" does not seem to work with fields in Word.

The format \# "#,#0.00" yields standard 3 digit groupings, just the same as \# "#,##0.00", so I suspect that the implementation of custom formats was limited!

Regards


Report •

#6
December 5, 2009 at 14:42:58
Hi,

An alternative is to enter numbers as normal with no digit groupings and then run a macro to add the digit groupings.

The following code creates digit pairs in the integer part of the number. The decimal part is left 'as-is'.

Please test this on copies of documents only. Note that the changes made by this code CANNOT be undone with Word's undo function.

Also this code has only been tested on the text shown below, so there may be unintended changes on other documents. So always backup your documents.

This code goes in a standard Module:
Enter the VBA window by clicking Alt + f11 (The left Alt key and function key #11 at the same time)
In the Project Explorer window (usually on left), find Project(YourDocument)
Right click on it and select Insert then Module (not Class Module)
Double click Module1 which is under the Modules folder
Enter the code in the main window.

Option Explicit

Private Sub DigitGp()
Dim rngWord As Range
Dim wdText As String
Dim strInt As String
Dim strGrouped As String
Dim intCom As Integer
Dim m, n As Integer

'go through each word in document
For Each rngWord In ActiveDocument.Words
    If intCom = 0 Then
        strInt = ""
        strGrouped = ""
        intCom = 0
        'test if word is a number
        If IsNumeric(rngWord.Text) Then
            'check it's not a decimal
            rngWord.Select
            If Selection.Previous(wdWord, 1) <> "." Then
                wdText = rngWord.Text
                'get integer (number never contains "." as this is a period in Word)
                strInt = Trim(wdText)
                'rebuild number with a "'" every two characters
                m = 2
                intCom = 2
                For n = Len(strInt) To 1 Step -1
                    strGrouped = Mid(strInt, n, 1) & strGrouped
                    m = m - 1
                    If m = 0 Then
                        'add a "," every two digits
                        strGrouped = "," & strGrouped
                        'keep track of number of ","s as word treats them as words! and
                        'we will need to jump over them and the new digit pairs
                        intCom = intCom + 2
                        m = 2
                    End If
                Next n
                'remove leading "," which occurs with even length numbers and add a space
                If Left(strGrouped, 1) = "," Then
                    strGrouped = " " & Right(strGrouped, Len(strGrouped) - 1)
                    Else
                    strGrouped = " " & strGrouped
                End If
                'delete this number and replace it with our new grouped number
                rngWord.Select
                Selection.Delete
                Selection.InsertAfter (strGrouped)
            End If
        End If
        Else
        intCom = intCom - 1
        If intCom < 0 Then intCom = 0
    End If
Next rngWord
End Sub

This was my Test document:

This is a test document for large numbers such as 9876543.21 and 109876543.21.
Digit grouping in pairs is required, such as 1122334455.66.

and this was the result after running the macro:
This is a test document for large numbers such as 9,87,65,43.21 and 1,09,87,65,43.21.
Digit grouping in pairs is required, such as 11,22,33,44,55.66.

Regards


Report •

#7
December 7, 2009 at 00:47:55
Thanks Humar for all your trouble, but the above is too much for me to handle nor am i capable of doing all this stuff. There should be a solution i feel. Most software's can make these changes somewhere. Is this not created in a macros or whatever it is i read somewhere? Can some normal.dot document be change? I googled here and there, but all half and bits explanation of the thing.

Report •

#8
December 7, 2009 at 06:14:13
Hi,

Response #6 is a Macro.

Just a thought - Is the digit grouping you are looking for, a standard digit grouping in a specific language? If so it might be available by changing the language setting.

Regards

Humar


Report •

#9
December 7, 2009 at 19:56:41
Hi Humar,

In this country (India) we represent numbers or Rupees in this way: 4,12,12,349.000, that is thousands are grouped in two digits each and not three digits how word puts it.

Please c if there is any solution or can a short-cut key be made.


Report •

#10
December 8, 2009 at 08:17:10
Hi,

I am not aware of a setting for formatting Rupees, but then it is not something that I have ever had to do. There is likely a solution available, but I don't know about it.

As to a short-cut key there still has to be a macro behind it to do the formatting.

As you are not in a position to install any code (response #7), then there is no point in me spending time writing any more code to run from a short-cut.

For my own interest, I did write a macro that can be run from a short-cut key, and it takes a number that you select in the document and formats it as requested "12,34,567.00"

If you are interested let me know, but you will have to copy and paste the macro to a module in normal.dot and assign a short cut key.

I can give you step by step instructions on how to do this.

Regards


Report •

#11
December 8, 2009 at 19:35:43
Hi Humar,

I think your solution 10 should solve my problem as you say i have to cut and paste it. Yes please, i would like to have this solution and also the steps to modify the normal.dot file.

Really am grateful to you and i am sorry if about response #7, it looks all to complicated to me.


Report •

#12
December 9, 2009 at 07:48:26
Hi,

Here is the code for a macro that will format a number as "##,"##0.00". Following the code is a step by step instruction for entering it and linking it to a short-cut key. I note that you have Word 2000, and I have developed this in Word 2003. As far as I know, the visual basic code works in Word 2000.

The macro will format either selected text e.g., 123456 or 123456.00 in a document or selected text in a table cell or a selected cell. It will not work if more than one cell is selected in a table, or a whole column or a complete table. These features could be added.

Macro:

Option Explicit
Public Sub Rupees()
Dim rngCell As Range
Dim strBfr As String
Dim strAft As String
Dim strInt As String
Dim strDec As String
Dim strFmInt As String
Dim strNumb As String
Dim strChar As String
Dim blnDec As Boolean
Dim m As Integer
Dim n As Integer

'If a single cell in a table is selected just get the text
If Selection.Information(wdWithInTable) Then
    If Selection.Cells.Count = 1 Then
        Selection.MoveEnd Unit:=wdCharacter, Count:=-1
        Else
        'more than a single cell - error out
        Exit Sub
    End If
 End If
 
With Selection
'make sure we are trying to format a number
If Not IsNumeric(Selection.Text) Then Exit Sub

'If its already got "," in it, take them out
'else just copy the selected text
If InStr(1, .Text, ",") > 0 Then
    For n = 1 To Len(.Text)
        strChar = Mid(.Text, n, 1)
        If strChar <> "," Then
            strNumb = strNumb & strChar
        End If
    Next n
    Else
    strNumb = .Text
End If
'remove any spaces
'but keep them to replace later
strBfr = ""
If Len(LTrim(strNumb)) <> Len(strNumb) Then strBfr = " "
strAft = ""
If Len(RTrim(strNumb)) <> Len(strNumb) Then strAft = " "
strNumb = Trim(strNumb)

'set flag to not in decimal part of number
blnDec = False
'split number into integer and decimal
For n = 1 To Len(strNumb)
    strChar = Mid(strNumb, n, 1)
    'if we get to a "." we have a decimal part of the number
    If strChar = "." Then blnDec = True
    If blnDec = False Then
        strInt = strInt & strChar
        Else
        strDec = strDec & strChar
    End If
Next n

'format integer as ##,##,##0
m = 1
For n = Len(strInt) To 1 Step -1
    strChar = Mid(strInt, n, 1)
    If m < 4 Then
        'first 3 digits all together
        strFmInt = strChar & strFmInt
        m = m + 1
        Else
        'test for even number of digits and insert ","
        If m / 2 = Int(m / 2) Then
            m = m + 1
            strFmInt = strChar & "," & strFmInt
            Else
            'not even, so just include the digit
            strFmInt = strChar & strFmInt
            m = m + 1
        End If
    End If
Next n

'replace selected number - make sure there is a space before it.
.Delete
If Selection.Text <> " " And strBfr = "" Then strBfr = " "
.InsertAfter (strBfr & strFmInt & strDec & strAft)
End With

End Sub

Steps to enter the Macro and assign short-cut key:
Open Word
Open the Visual Basic window, by clicking Alt+f11 (the left ALT key and function key 11 at the same time).
An alternative is Tools - Macros - Visual Basic Editor

On the left side you should see the Project Explorer window, with folders and document names. If you have a document open it will appear as Project(MyDocumentName). If the Project Explorer isn't visible, go to the Visual Basic window menu and select View - Project Explorer
Click on Normal (Normal has at least one folder below it labeled Microsoft Word Objects).Right click Normal ad select Insert and then choose Module (Not Class Module).
A new folder will open below Normal labeled Modules. Click + to see the new Module typically named Module 1. If you already had a Module 1 the new module would be Module 2.

Double click the new Module name in Project Explorer.
On the right side of the screen will be the window for the code (it has boxes with General and Declarations at the top of it.

Copy and paste the code into the window.
Select File - SaveNormal

Go back to the main Word document Window (Alt + f11 will swap you back)
From the menu select: Tools - Customize - Then the Commands Tab.
Click the Keyboard button
In the Categories list on the left, select Macros (you will likely have to scroll down - and the items are not in alphabetic order)
Having selected Macros, go to the right side and select Rupees
In the 'Press new shortcut key:' box enter the short-cut key combination (I used Alt + R).
Click Assign, Then Close and Close again.

In a blank document enter a number.
Select the whole number
Use your short-cut key (Alt+R)

If the short-cut key doesn't work, go back to the Visual Basic Window, (Alt +f11).
In the code window, below Option Explicit change the name Rupees to Rupees2

Go back to the main Word document window and allocate the short-cut key again, but this time select Rupees2.
(For some reason I had to do this as the short-cut key didn't work first time.)

Hope this works for you. Please post back for further assistance.

(I will try this out on Word 2000 later in the day).

Regards


Report •

#13
December 10, 2009 at 10:18:03
Hi,

I checked and the macro runs OK in Word 2000.
The process of allocating a shortcut key also works in Word 2000, and is the same (although there are some minor differences in the dialog box layouts).

Regards


Report •

#14
December 15, 2009 at 22:26:39
Hi Humar,
So sorry for the delay in replying back, I was busy and didn't get a chance to try it out.

Well it works perfectly well. I used the Alt+R key, and as you say i have to select the text and press the combination. Thank you very much for all your trouble and patience.

But now I tried selecting a few cells together in a table and then pressing the combination, but it only works one at a time. Is there a way it can be used as a switch like you showed me in another of my posts. Pressing Alt+F9 and then adding the switch,etc.

I will have a problem to format each cell so many times after mail merge. Is there something else that can be done to make a switch.

What about in the Table/Format Number Formula format drop down box to make it automatic for all.


Report •

#15
December 16, 2009 at 05:43:30
Hi,

When I wrote the code for this, I did not know that you had tables of numbers and as a result I did not include whole tables in the code.

I could write a new section, so that if you select a table, it will test every cell in the table for a number and then format all the numbers.

I am pretty busy at the moment, so it may be a little while before I can do this.

Regards


Report •

#16
December 16, 2009 at 19:23:07
I am very sorry Humar for not explaining to you exactly what i wanted. Please forgive me.

My work involves accounts and i work in word mostly. I use mail merge for salaries, payslips, etc. In my other posts on switches in merg, etc. all works fine. I mostly use table form to insert data. If there's something more information you require of me please let me know before you make the code.

Thank you Sir and hoping you will oblige me one more time when you can.


Report •

#17
December 17, 2009 at 15:10:18
Hi,

I have rewritten the Macro to allow you to convert whole tables.

Due to issues with tables in Word, I have limited the Macro to only converting single cells or whole tables (as well as selected text as before).

It is technically possible to make this work on groups of cells in a table or whole rows or columns, but it would require quite a lot more code.

Hopefully the complete table option will meet your needs. The Macro ignores cells in a table that are not numbers, and only tries to format numbers.

You will see that I have split the code into a subroutine and a function.
The function does the reformatting, whilst the subroutine handles finding numbers including cells in tables and replacing the numbers.

Put the function and subroutine in the same module. You can copy and paste the code between the Sub and End Sub lines over your existing sub routine. This will mean that the Alt+R shortcut will still work. Add the Function to End function code after the subroutine.

As before, please note that this macro has had very limited testing. Please test it on sample data.
As Undo does not work for Macros, always make a copy of your workbooks before running this Macro.

Option Explicit
Public Sub Rupees()
Dim rngCell As Cell
Dim strText As String

' test if in a table
If Selection.Information(wdWithInTable) Then
    'only handle a complete table or a single cell
    If Selection.Cells.Count = Selection.Tables(1).Range.Cells.Count Or _
            Selection.Range.Cells.Count = 1 Then
        ' in a table (single cell or whole table)
        For Each rngCell In Selection.Range.Cells
            rngCell.Select
            ' exclude end of cell marker from selection
            Selection.MoveEnd Unit:=wdCharacter, Count:=-1
                ' test that the cell contains a number
                If IsNumeric(Selection.Text) Then
                    ' save text, delete and replace with conversion
                    strText = Selection.Text
                    With Selection
                        .Delete
                        .InsertAfter (RupCnvt(strText))
                    End With
                End If
        Next rngCell
        Else
        MsgBox "Sorry this function only works" & vbCrLf & _
                "with single cells or" & vbCrLf & _
                "complete tables"
        Exit Sub
    End If
    Else
    ' not in a table
    ' make sure we are trying to format a number
    If Not IsNumeric(Selection.Text) Then Exit Sub
    ' replace selected number
    With Selection
        ' save text, delete and replace with conversion
        strText = .Text
        .Delete
        .InsertAfter (RupCnvt(strText))
    End With
End If
End Sub

Private Function RupCnvt(strNumb As String) As String
' function to convert text to text in Rupee format
Dim strBfr As String
Dim strAft As String
Dim strInt As String
Dim strDec As String
Dim strFmInt As String
Dim strChar As String
Dim blnDec As Boolean
Dim strTemp As String
Dim m As Integer
Dim n As Integer

' remove any spaces
' but keep them to replace later

On Error GoTo ErrHnd

strBfr = ""
If Len(LTrim(strNumb)) <> Len(strNumb) Then strBfr = " "
strAft = ""
If Len(RTrim(strNumb)) <> Len(strNumb) Then strAft = " "
strNumb = Trim(strNumb)

'If its already got "," in it, take them out
If InStr(1, strNumb, ",") > 0 Then
    strTemp = ""
    For n = 1 To Len(strNumb)
        strChar = Mid(strNumb, n, 1)
        If strChar <> "," Then
            strTemp = strTemp & strChar
        End If
    Next n
    strNumb = strTemp
End If

'set flag to not in decimal part of number
blnDec = False
'split number into integer and decimal
For n = 1 To Len(strNumb)
    strChar = Mid(strNumb, n, 1)
    'if we get to a "." we have a decimal part of the number
    If strChar = "." Then blnDec = True
    If blnDec = False Then
        strInt = strInt & strChar
        Else
        strDec = strDec & strChar
    End If
Next n

'format integer as ##,##,##0
m = 1
For n = Len(strInt) To 1 Step -1
    strChar = Mid(strInt, n, 1)
    If m < 4 Then
        'first 3 digits all together
        strFmInt = strChar & strFmInt
        m = m + 1
        Else
        'test for even number of digits and insert ","
        If m / 2 = Int(m / 2) Then
            m = m + 1
            strFmInt = strChar & "," & strFmInt
            Else
            'not even, so just include the digit
            strFmInt = strChar & strFmInt
            m = m + 1
        End If
    End If
Next n

RupCnvt = strBfr & strFmInt & strDec & strAft
Exit Function

'error handler
ErrHnd:
Err.Clear
'just return what we received
RupCnvt = strNumb
End Function

Regards


Report •

#18
December 17, 2009 at 21:20:12
.InsertAfter (strBfr & strFmInt & strDec & strAft)
End With
--------------> here right in the old one?
End Sub

Tell me if i have to add this macro as shown above.
and what do you mean by "Add the Function to End function code after the subroutine."

Please i am not a programmer, so have some patience with me.


Report •

#19
December 18, 2009 at 04:11:11
Hi,

Basically you are going to replace everything you already have with the new code I submitted.

My previous instructions were to reduce the possibility of losing the link to Alt+R.

However to avoid confusion, just delete all the code in the module and paste in the new code.

You may have to re-do the Alt+R shortcut. Just follow the instructions in Response #12.

Regards


Report •

#20
December 18, 2009 at 05:25:35
Hi,

Here is another Macro you might like to try.

It converts numbers in Fields.

Just paste the code into Module1
(Paste this as additional code - not a replaacement).
Link it to another shortcut key combination.

Public Sub RupFld()
Dim fldField As Field
Dim strText As String

For Each fldField In ActiveDocument.Fields
    If IsNumeric(fldField.Result.Text) Then
        strText = fldField.Result.Text
        fldField.Select
        fldField.Delete
        Selection.InsertAfter (RupCnvt(strText))
    End If
Next fldField
End Sub

This macro runs through all the fields in the active document and if they contain a number it converts the field to ordinary text, formated as ##,###.00

The macro does not affect headers and footers as far as I can tell. I have not tested this on much, as I do not have any documents available with merge fields - so give it a try on some test documents.

Regards


Report •

#21
December 22, 2009 at 01:42:49
Dear Humar,

Response 17 works perfectly just as you said, only i have to mark the whole table for its effect as Column-wise does not work. But its great and Thanks. You have saved me a lot of trouble.

Response 20 gives the same effect. I gave the second combination as ALT+B along with ALT+R in the Macros. But i don't know how to use it with merge fields like a switch. Can you give me an example. I tried different ways and methods. There's something i am doing wrong.


Report •

#22
December 23, 2009 at 15:12:31
Hi,

The Alt-B macro should just run !

On a document with merged fields containing / displaying numbers, place the cursor anywhere on the page. Then hit Alt-B, and the merged fields with numbers should be converted to text formatted as ##,###.00

If nothing happens, it is possible that Alt-B hasn't linked to the new routine.

Alt-B should not change anything except field data. If Alt-B works on a table for example, then you haven't got the proper link between Alt-B and the Macro named RupFld.

Another test is to have a document with merge fields containing numbers, open. Then go to the VBA window, click anywhere inside the RupFld subroutine and hit f5.

Let me know what happens.

Regards


Report •

#23
December 23, 2009 at 20:04:46
Humar,

Thanks very much for your help. The Alt+B key works and it runs through the whole table and changes the numbers to ##,###.00 format and also other numbers as well, but what can i do. I feel bad to go on troubling you.

Oh well, what else can i say. I thank you very much for being patient with me. Wishing you a happy new year ahead and thanks.


Report •

#24
December 24, 2009 at 04:44:08
Hi,

This means that Alt+B is linked to the wrong code.

Open a document which contains some merge fields which display numbers.

Use Alt+f11 and find the new code RupFld.
Click anywhere inside the RupFld code and hit the f5 key.

The fields in the document should change to the new format BUT any other numbers should be unchanged.

The new code (RupFld) only changes numbers in fields.

Regards


Report •


Ask Question