Setting Decimal Places in a Word Table

[Lisa]

I have a Word 2007 table with two columns that contain numbers. My client
wants these numbers to reflect one decimal place. This document is fielded to
serveral locations and I do not wish to involve Excel in "importing a table".
There has to be a simple way to accomplish this. Maybe a macro?

 

[macropod]

Hi Lisa,

There's no auto-formatting, so anything you do (short of using formfields and protecting the document for forms) won't result in
numbers that are input into the table being auto-formatted. With that proviso, try the following macro:
Sub NumberFormatter()
Application.ScreenUpdating = False
Dim oCel As Cell, RngCel As Range, strBM As String, strChr, strCurr As String, i As Integer
'currency symbols
strCurr = "$,€,£,¥"
With Selection
'check that the selection is in a table
If .Information(wdWithInTable) = True Then
'check each cell in the table
For Each oCel In .Tables(1).Range.Cells
'get each cell's contents, excluding the cell marker
Set RngCel = oCel.Range
RngCel.End = RngCel.End - 1
'check for are any fields in the cell
If RngCel.Fields.Count = 0 Then
'clear the temporary bookmark store
strBM = ""
' check for any bookmarks pertaining to the cell
If RngCel.Bookmarks.Count > 0 Then
'capture the first bookmark that exactly matches this cell
If RngCel.Bookmarks(1).Range = RngCel Then strBM = RngCel.Bookmarks(1).Name
End If
'check that the cell contains a number - this includes currency in a format
'matching the system's regional settings
If IsNumeric(RngCel.Text) = True Then
'skip over percentages
If Right(RngCel.Text, 1) <> "%" Then
'test whether the first character is the local currency symbol
strChr = Left(RngCel.Text, 1)
If Not IsNumeric(strChr) Then
'currency symbol found, so reformat the value with that symbol
RngCel.Text = strChr & Format(RngCel.Text, "#,##0.00")
Else
'currency symbol not found, so reformat the value
RngCel.Text = Format(RngCel.Text, "#,##0.00")
End If
End If
Else
'check for other currencies in cells that aren't automatically assessed as values
For i = 0 To UBound(Split(strCurr, ","))
'test whether the first character is a currency symbol
If Left(RngCel.Text, 1) = Split(strCurr, ",")(i) Then
'store the cell text, minus the currency symbol
strChr = Replace(RngCel.Text, Split(strCurr, ",")(i), "")
'check that the rest of the cell contains a number
If IsNumeric(strChr) = True Then
'currency symbol found, so reformat the value with that symbol
RngCel.Text = Split(strCurr, ",")(i) & Format(strChr, "#,##0.00")
End If
End If
Next
End If
'restore the bookmark if there was one
If strBM <> "" Then ActiveDocument.Bookmarks.Add strBM, RngCel
End If
Next
'update any fields in the table
.Fields.Update
End If
End With
'update fields elsewhere in the body of the document
ActiveDocument.PrintPreview
ActiveDocument.ClosePrintPreview
Application.ScreenUpdating = True
End Sub

As coded, the macro will preserve a:
.. bookmark applied to any cell value (eg for cross-referencing purposes)
.. field-coded formulae
.. cross-references, and
leaves values formatted as percentages, dates & times alone. Currency values and all other values are converted with a comma for a
thousands separator and a period for a decimal separator. If you have other currency forms, you can simply add them to the 'strCurr'
variable. Decimals are to two places. Cross-references within the table and the body of the document generally are also updated.

 

[macropod]

Hi Lisa,

You might get better results by changing .Fields.Update to .Tables(1).Range.Fields.Update

--
Cheers
macropod
[Microsoft MVP - Word]

Hi Lisa,

There's no auto-formatting, so anything you do (short of using formfields and protecting the document for forms) won't result in
numbers that are input into the table being auto-formatted. With that proviso, try the following macro:
Sub NumberFormatter()
Application.ScreenUpdating = False
Dim oCel As Cell, RngCel As Range, strBM As String, strChr, strCurr As String, i As Integer
'currency symbols
strCurr = "$,€,£,¥"
With Selection
'check that the selection is in a table
If .Information(wdWithInTable) = True Then
'check each cell in the table
For Each oCel In .Tables(1).Range.Cells
'get each cell's contents, excluding the cell marker
Set RngCel = oCel.Range
RngCel.End = RngCel.End - 1
'check for are any fields in the cell
If RngCel.Fields.Count = 0 Then
'clear the temporary bookmark store
strBM = ""
' check for any bookmarks pertaining to the cell
If RngCel.Bookmarks.Count > 0 Then
'capture the first bookmark that exactly matches this cell
If RngCel.Bookmarks(1).Range = RngCel Then strBM = RngCel.Bookmarks(1).Name
End If
'check that the cell contains a number - this includes currency in a format
'matching the system's regional settings
If IsNumeric(RngCel.Text) = True Then
'skip over percentages
If Right(RngCel.Text, 1) <> "%" Then
'test whether the first character is the local currency symbol
strChr = Left(RngCel.Text, 1)
If Not IsNumeric(strChr) Then
'currency symbol found, so reformat the value with that symbol
RngCel.Text = strChr & Format(RngCel.Text, "#,##0.00")
Else
'currency symbol not found, so reformat the value
RngCel.Text = Format(RngCel.Text, "#,##0.00")
End If
End If
Else
'check for other currencies in cells that aren't automatically assessed as values
For i = 0 To UBound(Split(strCurr, ","))
'test whether the first character is a currency symbol
If Left(RngCel.Text, 1) = Split(strCurr, ",")(i) Then
'store the cell text, minus the currency symbol
strChr = Replace(RngCel.Text, Split(strCurr, ",")(i), "")
'check that the rest of the cell contains a number
If IsNumeric(strChr) = True Then
'currency symbol found, so reformat the value with that symbol
RngCel.Text = Split(strCurr, ",")(i) & Format(strChr, "#,##0.00")
End If
End If
Next
End If
'restore the bookmark if there was one
If strBM <> "" Then ActiveDocument.Bookmarks.Add strBM, RngCel
End If
Next
'update any fields in the table
.Fields.Update
End If
End With
'update fields elsewhere in the body of the document
ActiveDocument.PrintPreview
ActiveDocument.ClosePrintPreview
Application.ScreenUpdating = True
End Sub

As coded, the macro will preserve a:
. bookmark applied to any cell value (eg for cross-referencing purposes)
. field-coded formulae
. cross-references, and
leaves values formatted as percentages, dates & times alone. Currency values and all other values are converted with a comma for
a thousands separator and a period for a decimal separator. If you have other currency forms, you can simply add them to the
'strCurr' variable. Decimals are to two places. Cross-references within the table and the body of the document generally are also
updated.

--
Cheers
macropod
[Microsoft MVP - Word]

I have a Word 2007 table with two columns that contain numbers. My client
wants these numbers to reflect one decimal place. This document is fielded to
serveral locations and I do not wish to involve Excel in "importing a table".
There has to be a simple way to accomplish this. Maybe a macro?

 

[Lisa]

Wow - thanks macropod! I'm a tech writer not a programmer, so your code is
greatly appreciated! I'll try it out and let you know how it works for the
document.
--
Lisa Stewart
Technical Writer
Yulista Management Services
Huntsville, AL

Hi Lisa,

You might get better results by changing .Fields.Update to .Tables(1).Range.Fields.Update

--
Cheers
macropod
[Microsoft MVP - Word]

Hi Lisa,

There's no auto-formatting, so anything you do (short of using formfields and protecting the document for forms) won't result in
numbers that are input into the table being auto-formatted. With that proviso, try the following macro:
Sub NumberFormatter()
Application.ScreenUpdating = False
Dim oCel As Cell, RngCel As Range, strBM As String, strChr, strCurr As String, i As Integer
'currency symbols
strCurr = "$,€,£,¥"
With Selection
'check that the selection is in a table
If .Information(wdWithInTable) = True Then
'check each cell in the table
For Each oCel In .Tables(1).Range.Cells
'get each cell's contents, excluding the cell marker
Set RngCel = oCel.Range
RngCel.End = RngCel.End - 1
'check for are any fields in the cell
If RngCel.Fields.Count = 0 Then
'clear the temporary bookmark store
strBM = ""
' check for any bookmarks pertaining to the cell
If RngCel.Bookmarks.Count > 0 Then
'capture the first bookmark that exactly matches this cell
If RngCel.Bookmarks(1).Range = RngCel Then strBM = RngCel.Bookmarks(1).Name
End If
'check that the cell contains a number - this includes currency in a format
'matching the system's regional settings
If IsNumeric(RngCel.Text) = True Then
'skip over percentages
If Right(RngCel.Text, 1) <> "%" Then
'test whether the first character is the local currency symbol
strChr = Left(RngCel.Text, 1)
If Not IsNumeric(strChr) Then
'currency symbol found, so reformat the value with that symbol
RngCel.Text = strChr & Format(RngCel.Text, "#,##0.00")
Else
'currency symbol not found, so reformat the value
RngCel.Text = Format(RngCel.Text, "#,##0.00")
End If
End If
Else
'check for other currencies in cells that aren't automatically assessed as values
For i = 0 To UBound(Split(strCurr, ","))
'test whether the first character is a currency symbol
If Left(RngCel.Text, 1) = Split(strCurr, ",")(i) Then
'store the cell text, minus the currency symbol
strChr = Replace(RngCel.Text, Split(strCurr, ",")(i), "")
'check that the rest of the cell contains a number
If IsNumeric(strChr) = True Then
'currency symbol found, so reformat the value with that symbol
RngCel.Text = Split(strCurr, ",")(i) & Format(strChr, "#,##0.00")
End If
End If
Next
End If
'restore the bookmark if there was one
If strBM <> "" Then ActiveDocument.Bookmarks.Add strBM, RngCel
End If
Next
'update any fields in the table
.Fields.Update
End If
End With
'update fields elsewhere in the body of the document
ActiveDocument.PrintPreview
ActiveDocument.ClosePrintPreview
Application.ScreenUpdating = True
End Sub

As coded, the macro will preserve a:
. bookmark applied to any cell value (eg for cross-referencing purposes)
. field-coded formulae
. cross-references, and
leaves values formatted as percentages, dates & times alone. Currency values and all other values are converted with a comma for
a thousands separator and a period for a decimal separator. If you have other currency forms, you can simply add them to the
'strCurr' variable. Decimals are to two places. Cross-references within the table and the body of the document generally are also
updated.

--
Cheers
macropod
[Microsoft MVP - Word]

I have a Word 2007 table with two columns that contain numbers. My client
wants these numbers to reflect one decimal place. This document is fielded to
serveral locations and I do not wish to involve Excel in "importing a table".
There has to be a simple way to accomplish this. Maybe a macro?

.

 

[Lisa]

OK - I've finally had the chance to create the macro with the code you
provided. Before I run it, do I select the entire table, just the columns
with numbers in them, or just the cells I want the macro to affect?

Thanks.
--
Lisa Stewart
Technical Writer
Yulista Management Services
Huntsville, AL

Hi Lisa,

You might get better results by changing .Fields.Update to .Tables(1).Range.Fields.Update

--
Cheers
macropod
[Microsoft MVP - Word]

Hi Lisa,

There's no auto-formatting, so anything you do (short of using formfields and protecting the document for forms) won't result in
numbers that are input into the table being auto-formatted. With that proviso, try the following macro:
Sub NumberFormatter()
Application.ScreenUpdating = False
Dim oCel As Cell, RngCel As Range, strBM As String, strChr, strCurr As String, i As Integer
'currency symbols
strCurr = "$,€,£,¥"
With Selection
'check that the selection is in a table
If .Information(wdWithInTable) = True Then
'check each cell in the table
For Each oCel In .Tables(1).Range.Cells
'get each cell's contents, excluding the cell marker
Set RngCel = oCel.Range
RngCel.End = RngCel.End - 1
'check for are any fields in the cell
If RngCel.Fields.Count = 0 Then
'clear the temporary bookmark store
strBM = ""
' check for any bookmarks pertaining to the cell
If RngCel.Bookmarks.Count > 0 Then
'capture the first bookmark that exactly matches this cell
If RngCel.Bookmarks(1).Range = RngCel Then strBM = RngCel.Bookmarks(1).Name
End If
'check that the cell contains a number - this includes currency in a format
'matching the system's regional settings
If IsNumeric(RngCel.Text) = True Then
'skip over percentages
If Right(RngCel.Text, 1) <> "%" Then
'test whether the first character is the local currency symbol
strChr = Left(RngCel.Text, 1)
If Not IsNumeric(strChr) Then
'currency symbol found, so reformat the value with that symbol
RngCel.Text = strChr & Format(RngCel.Text, "#,##0.00")
Else
'currency symbol not found, so reformat the value
RngCel.Text = Format(RngCel.Text, "#,##0.00")
End If
End If
Else
'check for other currencies in cells that aren't automatically assessed as values
For i = 0 To UBound(Split(strCurr, ","))
'test whether the first character is a currency symbol
If Left(RngCel.Text, 1) = Split(strCurr, ",")(i) Then
'store the cell text, minus the currency symbol
strChr = Replace(RngCel.Text, Split(strCurr, ",")(i), "")
'check that the rest of the cell contains a number
If IsNumeric(strChr) = True Then
'currency symbol found, so reformat the value with that symbol
RngCel.Text = Split(strCurr, ",")(i) & Format(strChr, "#,##0.00")
End If
End If
Next
End If
'restore the bookmark if there was one
If strBM <> "" Then ActiveDocument.Bookmarks.Add strBM, RngCel
End If
Next
'update any fields in the table
.Fields.Update
End If
End With
'update fields elsewhere in the body of the document
ActiveDocument.PrintPreview
ActiveDocument.ClosePrintPreview
Application.ScreenUpdating = True
End Sub

As coded, the macro will preserve a:
. bookmark applied to any cell value (eg for cross-referencing purposes)
. field-coded formulae
. cross-references, and
leaves values formatted as percentages, dates & times alone. Currency values and all other values are converted with a comma for
a thousands separator and a period for a decimal separator. If you have other currency forms, you can simply add them to the
'strCurr' variable. Decimals are to two places. Cross-references within the table and the body of the document generally are also
updated.

--
Cheers
macropod
[Microsoft MVP - Word]

I have a Word 2007 table with two columns that contain numbers. My client
wants these numbers to reflect one decimal place. This document is fielded to
serveral locations and I do not wish to involve Excel in "importing a table".
There has to be a simple way to accomplish this. Maybe a macro?

.

 

[macropod]

Hi Lisa,

Provided any part of a table is selected,the macro processes the whole table.

--
Cheers
macropod
[Microsoft MVP - Word]

OK - I've finally had the chance to create the macro with the code you
provided. Before I run it, do I select the entire table, just the columns
with numbers in them, or just the cells I want the macro to affect?

Thanks.
--
Lisa Stewart
Technical Writer
Yulista Management Services
Huntsville, AL

Hi Lisa,

You might get better results by changing .Fields.Update to .Tables(1).Range.Fields.Update

--
Cheers
macropod
[Microsoft MVP - Word]

Hi Lisa,

There's no auto-formatting, so anything you do (short of using formfields and protecting the document for forms) won't result
in
numbers that are input into the table being auto-formatted. With that proviso, try the following macro:
Sub NumberFormatter()
Application.ScreenUpdating = False
Dim oCel As Cell, RngCel As Range, strBM As String, strChr, strCurr As String, i As Integer
'currency symbols
strCurr = "$,€,£,¥"
With Selection
'check that the selection is in a table
If .Information(wdWithInTable) = True Then
'check each cell in the table
For Each oCel In .Tables(1).Range.Cells
'get each cell's contents, excluding the cell marker
Set RngCel = oCel.Range
RngCel.End = RngCel.End - 1
'check for are any fields in the cell
If RngCel.Fields.Count = 0 Then
'clear the temporary bookmark store
strBM = ""
' check for any bookmarks pertaining to the cell
If RngCel.Bookmarks.Count > 0 Then
'capture the first bookmark that exactly matches this cell
If RngCel.Bookmarks(1).Range = RngCel Then strBM = RngCel.Bookmarks(1).Name
End If
'check that the cell contains a number - this includes currency in a format
'matching the system's regional settings
If IsNumeric(RngCel.Text) = True Then
'skip over percentages
If Right(RngCel.Text, 1) <> "%" Then
'test whether the first character is the local currency symbol
strChr = Left(RngCel.Text, 1)
If Not IsNumeric(strChr) Then
'currency symbol found, so reformat the value with that symbol
RngCel.Text = strChr & Format(RngCel.Text, "#,##0.00")
Else
'currency symbol not found, so reformat the value
RngCel.Text = Format(RngCel.Text, "#,##0.00")
End If
End If
Else
'check for other currencies in cells that aren't automatically assessed as values
For i = 0 To UBound(Split(strCurr, ","))
'test whether the first character is a currency symbol
If Left(RngCel.Text, 1) = Split(strCurr, ",")(i) Then
'store the cell text, minus the currency symbol
strChr = Replace(RngCel.Text, Split(strCurr, ",")(i), "")
'check that the rest of the cell contains a number
If IsNumeric(strChr) = True Then
'currency symbol found, so reformat the value with that symbol
RngCel.Text = Split(strCurr, ",")(i) & Format(strChr, "#,##0.00")
End If
End If
Next
End If
'restore the bookmark if there was one
If strBM <> "" Then ActiveDocument.Bookmarks.Add strBM, RngCel
End If
Next
'update any fields in the table
.Fields.Update
End If
End With
'update fields elsewhere in the body of the document
ActiveDocument.PrintPreview
ActiveDocument.ClosePrintPreview
Application.ScreenUpdating = True
End Sub

As coded, the macro will preserve a:
. bookmark applied to any cell value (eg for cross-referencing purposes)
. field-coded formulae
. cross-references, and
leaves values formatted as percentages, dates & times alone. Currency values and all other values are converted with a comma
for
a thousands separator and a period for a decimal separator. If you have other currency forms, you can simply add them to the
'strCurr' variable. Decimals are to two places. Cross-references within the table and the body of the document generally are
also
updated.

--
Cheers
macropod
[Microsoft MVP - Word]


I have a Word 2007 table with two columns that contain numbers. My client
wants these numbers to reflect one decimal place. This document is fielded to
serveral locations and I do not wish to involve Excel in "importing a table".
There has to be a simple way to accomplish this. Maybe a macro?

.

 

[macropod]

Hi Lisa,

To make the macro work on only the selected cells, change:
..Tables(1).Range.Cells
to
..Cells

To make the macro work on all cells in all tables in the body of the document,
1. add:
, oTbl As Table
to the line beginning with
Dim
2. change:
With Selection
'check that the selection is in a table
If .Information(wdWithInTable) = True Then
'check each cell in the table
For Each oCel In .Tables(1).Range.Cells
to:
With ActiveDocument
'loop through all tables
For Each oTbl In .Tables
'check each cell in the table
For Each oCel In oTbl.Range.Cells
3. and change:
.Fields.Update (or .Tables(1).Range.Fields.Update)
End if
End With
or:
.Tables(1).Range.Fields.Update
End if
End With
to:
.Fields.Update
Next
End With



--
Cheers
macropod
[Microsoft MVP - Word]

Hi Lisa,

Provided any part of a table is selected,the macro processes the whole table.

--
Cheers
macropod
[Microsoft MVP - Word]

OK - I've finally had the chance to create the macro with the code you
provided. Before I run it, do I select the entire table, just the columns
with numbers in them, or just the cells I want the macro to affect?

Thanks.
--
Lisa Stewart
Technical Writer
Yulista Management Services
Huntsville, AL

Hi Lisa,

You might get better results by changing .Fields.Update to .Tables(1).Range.Fields.Update

--
Cheers
macropod
[Microsoft MVP - Word]


Hi Lisa,

There's no auto-formatting, so anything you do (short of using formfields and protecting the document for forms) won't result
in
numbers that are input into the table being auto-formatted. With that proviso, try the following macro:
Sub NumberFormatter()
Application.ScreenUpdating = False
Dim oCel As Cell, RngCel As Range, strBM As String, strChr, strCurr As String, i As Integer
'currency symbols
strCurr = "$,€,£,¥"
With Selection
'check that the selection is in a table
If .Information(wdWithInTable) = True Then
'check each cell in the table
For Each oCel In .Tables(1).Range.Cells
'get each cell's contents, excluding the cell marker
Set RngCel = oCel.Range
RngCel.End = RngCel.End - 1
'check for are any fields in the cell
If RngCel.Fields.Count = 0 Then
'clear the temporary bookmark store
strBM = ""
' check for any bookmarks pertaining to the cell
If RngCel.Bookmarks.Count > 0 Then
'capture the first bookmark that exactly matches this cell
If RngCel.Bookmarks(1).Range = RngCel Then strBM = RngCel.Bookmarks(1).Name
End If
'check that the cell contains a number - this includes currency in a format
'matching the system's regional settings
If IsNumeric(RngCel.Text) = True Then
'skip over percentages
If Right(RngCel.Text, 1) <> "%" Then
'test whether the first character is the local currency symbol
strChr = Left(RngCel.Text, 1)
If Not IsNumeric(strChr) Then
'currency symbol found, so reformat the value with that symbol
RngCel.Text = strChr & Format(RngCel.Text, "#,##0.00")
Else
'currency symbol not found, so reformat the value
RngCel.Text = Format(RngCel.Text, "#,##0.00")
End If
End If
Else
'check for other currencies in cells that aren't automatically assessed as values
For i = 0 To UBound(Split(strCurr, ","))
'test whether the first character is a currency symbol
If Left(RngCel.Text, 1) = Split(strCurr, ",")(i) Then
'store the cell text, minus the currency symbol
strChr = Replace(RngCel.Text, Split(strCurr, ",")(i), "")
'check that the rest of the cell contains a number
If IsNumeric(strChr) = True Then
'currency symbol found, so reformat the value with that symbol
RngCel.Text = Split(strCurr, ",")(i) & Format(strChr, "#,##0.00")
End If
End If
Next
End If
'restore the bookmark if there was one
If strBM <> "" Then ActiveDocument.Bookmarks.Add strBM, RngCel
End If
Next
'update any fields in the table
.Fields.Update
End If
End With
'update fields elsewhere in the body of the document
ActiveDocument.PrintPreview
ActiveDocument.ClosePrintPreview
Application.ScreenUpdating = True
End Sub

As coded, the macro will preserve a:
. bookmark applied to any cell value (eg for cross-referencing purposes)
. field-coded formulae
. cross-references, and
leaves values formatted as percentages, dates & times alone. Currency values and all other values are converted with a comma
for
a thousands separator and a period for a decimal separator. If you have other currency forms, you can simply add them to the
'strCurr' variable. Decimals are to two places. Cross-references within the table and the body of the document generally are
also
updated.

--
Cheers
macropod
[Microsoft MVP - Word]


I have a Word 2007 table with two columns that contain numbers. My client
wants these numbers to reflect one decimal place. This document is fielded to
serveral locations and I do not wish to involve Excel in "importing a table".
There has to be a simple way to accomplish this. Maybe a macro?


.

 

[Lisa]

Macropod - thanks. I've finally had the chance to run the macro with the
parameters I needed. You're great!

I do have one small ?: The below 3 columns respresent the columns I was
editing. Only the numbers with ".0" are the numbers I applied the macro to.
My ? is - although I selected all the numbers in col 2 and 5 numbers in col
1, the macro changed only 1 number in col 1. If I selected only the numbers
in col 2 and ran the macro then it changed the numbers as it was intended. Is
the macro running correctly?

WEIGHT ARM MOM/1000
8.0 176.0 1.4
7.8 230.0 1.8
2.2 176.0 0.4
2.5 203.0 0.5
3.0 358.0 1.1
1.0 176.0 0.2
0.3 176.0 0.1
2.3 203.0 0.5
1.0 212.0 0.2
0.9 212.0 0.2
0.3 225.0 0.1
1.4 331.0 0.5
12.0 176.0 2.1

--
Lisa Stewart
Technical Writer
Yulista Management Services
Huntsville, AL

Hi Lisa,

To make the macro work on only the selected cells, change:
..Tables(1).Range.Cells
to
..Cells

To make the macro work on all cells in all tables in the body of the document,
1. add:
, oTbl As Table
to the line beginning with
Dim
2. change:
With Selection
'check that the selection is in a table
If .Information(wdWithInTable) = True Then
'check each cell in the table
For Each oCel In .Tables(1).Range.Cells
to:
With ActiveDocument
'loop through all tables
For Each oTbl In .Tables
'check each cell in the table
For Each oCel In oTbl.Range.Cells
3. and change:
.Fields.Update (or .Tables(1).Range.Fields.Update)
End if
End With
or:
.Tables(1).Range.Fields.Update
End if
End With
to:
.Fields.Update
Next
End With



--
Cheers
macropod
[Microsoft MVP - Word]

Hi Lisa,

Provided any part of a table is selected,the macro processes the whole table.

--
Cheers
macropod
[Microsoft MVP - Word]

OK - I've finally had the chance to create the macro with the code you
provided. Before I run it, do I select the entire table, just the columns
with numbers in them, or just the cells I want the macro to affect?

Thanks.
--
Lisa Stewart
Technical Writer
Yulista Management Services
Huntsville, AL



:

Hi Lisa,

You might get better results by changing .Fields.Update to .Tables(1).Range.Fields.Update

--
Cheers
macropod
[Microsoft MVP - Word]


Hi Lisa,

There's no auto-formatting, so anything you do (short of using formfields and protecting the document for forms) won't result
in
numbers that are input into the table being auto-formatted. With that proviso, try the following macro:
Sub NumberFormatter()
Application.ScreenUpdating = False
Dim oCel As Cell, RngCel As Range, strBM As String, strChr, strCurr As String, i As Integer
'currency symbols
strCurr = "$,€,£,¥"
With Selection
'check that the selection is in a table
If .Information(wdWithInTable) = True Then
'check each cell in the table
For Each oCel In .Tables(1).Range.Cells
'get each cell's contents, excluding the cell marker
Set RngCel = oCel.Range
RngCel.End = RngCel.End - 1
'check for are any fields in the cell
If RngCel.Fields.Count = 0 Then
'clear the temporary bookmark store
strBM = ""
' check for any bookmarks pertaining to the cell
If RngCel.Bookmarks.Count > 0 Then
'capture the first bookmark that exactly matches this cell
If RngCel.Bookmarks(1).Range = RngCel Then strBM = RngCel.Bookmarks(1).Name
End If
'check that the cell contains a number - this includes currency in a format
'matching the system's regional settings
If IsNumeric(RngCel.Text) = True Then
'skip over percentages
If Right(RngCel.Text, 1) <> "%" Then
'test whether the first character is the local currency symbol
strChr = Left(RngCel.Text, 1)
If Not IsNumeric(strChr) Then
'currency symbol found, so reformat the value with that symbol
RngCel.Text = strChr & Format(RngCel.Text, "#,##0.00")
Else
'currency symbol not found, so reformat the value
RngCel.Text = Format(RngCel.Text, "#,##0.00")
End If
End If
Else
'check for other currencies in cells that aren't automatically assessed as values
For i = 0 To UBound(Split(strCurr, ","))
'test whether the first character is a currency symbol
If Left(RngCel.Text, 1) = Split(strCurr, ",")(i) Then
'store the cell text, minus the currency symbol
strChr = Replace(RngCel.Text, Split(strCurr, ",")(i), "")
'check that the rest of the cell contains a number
If IsNumeric(strChr) = True Then
'currency symbol found, so reformat the value with that symbol
RngCel.Text = Split(strCurr, ",")(i) & Format(strChr, "#,##0.00")
End If
End If
Next
End If
'restore the bookmark if there was one
If strBM <> "" Then ActiveDocument.Bookmarks.Add strBM, RngCel
End If
Next
'update any fields in the table
.Fields.Update
End If
End With
'update fields elsewhere in the body of the document
ActiveDocument.PrintPreview
ActiveDocument.ClosePrintPreview
Application.ScreenUpdating = True
End Sub

As coded, the macro will preserve a:
. bookmark applied to any cell value (eg for cross-referencing purposes)
. field-coded formulae
. cross-references, and
leaves values formatted as percentages, dates & times alone. Currency values and all other values are converted with a comma
for
a thousands separator and a period for a decimal separator. If you have other currency forms, you can simply add them to the
'strCurr' variable. Decimals are to two places. Cross-references within the table and the body of the document generally are
also
updated.

--
Cheers
macropod
[Microsoft MVP - Word]


I have a Word 2007 table with two columns that contain numbers. My client
wants these numbers to reflect one decimal place. This document is fielded to
serveral locations and I do not wish to involve Excel in "importing a table".
There has to be a simple way to accomplish this. Maybe a macro?


.

.

 

[Doug Robbins - Word MVP]

I believe that macropod has gone off on a road trip so he might not be back
here for a while.

I guess that you selected the 5 numbers in col 1 by using Ctrl+Left Click,
which does allow you to select non-contiguous ranges. However, a macro can
only work on a contiguous range and that is the reason that it only affects
the first of the selected cells.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Macropod - thanks. I've finally had the chance to run the macro with the
parameters I needed. You're great!

I do have one small ?: The below 3 columns respresent the columns I was
editing. Only the numbers with ".0" are the numbers I applied the macro
to.
My ? is - although I selected all the numbers in col 2 and 5 numbers in
col
1, the macro changed only 1 number in col 1. If I selected only the
numbers
in col 2 and ran the macro then it changed the numbers as it was intended.
Is
the macro running correctly?

WEIGHT ARM MOM/1000
8.0 176.0 1.4
7.8 230.0 1.8
2.2 176.0 0.4
2.5 203.0 0.5
3.0 358.0 1.1
1.0 176.0 0.2
0.3 176.0 0.1
2.3 203.0 0.5
1.0 212.0 0.2
0.9 212.0 0.2
0.3 225.0 0.1
1.4 331.0 0.5
12.0 176.0 2.1

--
Lisa Stewart
Technical Writer
Yulista Management Services
Huntsville, AL

Hi Lisa,

To make the macro work on only the selected cells, change:
..Tables(1).Range.Cells
to
..Cells

To make the macro work on all cells in all tables in the body of the
document,
1. add:
, oTbl As Table
to the line beginning with
Dim
2. change:
With Selection
'check that the selection is in a table
If .Information(wdWithInTable) = True Then
'check each cell in the table
For Each oCel In .Tables(1).Range.Cells
to:
With ActiveDocument
'loop through all tables
For Each oTbl In .Tables
'check each cell in the table
For Each oCel In oTbl.Range.Cells
3. and change:
.Fields.Update (or .Tables(1).Range.Fields.Update)
End if
End With
or:
.Tables(1).Range.Fields.Update
End if
End With
to:
.Fields.Update
Next
End With



--
Cheers
macropod
[Microsoft MVP - Word]

Hi Lisa,

Provided any part of a table is selected,the macro processes the whole
table.

--
Cheers
macropod
[Microsoft MVP - Word]


OK - I've finally had the chance to create the macro with the code you
provided. Before I run it, do I select the entire table, just the
columns
with numbers in them, or just the cells I want the macro to affect?

Thanks.
--
Lisa Stewart
Technical Writer
Yulista Management Services
Huntsville, AL



:

Hi Lisa,

You might get better results by changing .Fields.Update to
.Tables(1).Range.Fields.Update

--
Cheers
macropod
[Microsoft MVP - Word]


Hi Lisa,

There's no auto-formatting, so anything you do (short of using
formfields and protecting the document for forms) won't result
in
numbers that are input into the table being auto-formatted. With
that proviso, try the following macro:
Sub NumberFormatter()
Application.ScreenUpdating = False
Dim oCel As Cell, RngCel As Range, strBM As String, strChr, strCurr
As String, i As Integer
'currency symbols
strCurr = "$,€,£,¥"
With Selection
'check that the selection is in a table
If .Information(wdWithInTable) = True Then
'check each cell in the table
For Each oCel In .Tables(1).Range.Cells
'get each cell's contents, excluding the cell marker
Set RngCel = oCel.Range
RngCel.End = RngCel.End - 1
'check for are any fields in the cell
If RngCel.Fields.Count = 0 Then
'clear the temporary bookmark store
strBM = ""
' check for any bookmarks pertaining to the cell
If RngCel.Bookmarks.Count > 0 Then
'capture the first bookmark that exactly matches this cell
If RngCel.Bookmarks(1).Range = RngCel Then strBM =
RngCel.Bookmarks(1).Name
End If
'check that the cell contains a number - this includes
currency in a format
'matching the system's regional settings
If IsNumeric(RngCel.Text) = True Then
'skip over percentages
If Right(RngCel.Text, 1) <> "%" Then
'test whether the first character is the local currency
symbol
strChr = Left(RngCel.Text, 1)
If Not IsNumeric(strChr) Then
'currency symbol found, so reformat the value with
that symbol
RngCel.Text = strChr & Format(RngCel.Text, "#,##0.00")
Else
'currency symbol not found, so reformat the value
RngCel.Text = Format(RngCel.Text, "#,##0.00")
End If
End If
Else
'check for other currencies in cells that aren't
automatically assessed as values
For i = 0 To UBound(Split(strCurr, ","))
'test whether the first character is a currency symbol
If Left(RngCel.Text, 1) = Split(strCurr, ",")(i) Then
'store the cell text, minus the currency symbol
strChr = Replace(RngCel.Text, Split(strCurr, ",")(i),
"")
'check that the rest of the cell contains a number
If IsNumeric(strChr) = True Then
'currency symbol found, so reformat the value with
that symbol
RngCel.Text = Split(strCurr, ",")(i) &
Format(strChr, "#,##0.00")
End If
End If
Next
End If
'restore the bookmark if there was one
If strBM <> "" Then ActiveDocument.Bookmarks.Add strBM,
RngCel
End If
Next
'update any fields in the table
.Fields.Update
End If
End With
'update fields elsewhere in the body of the document
ActiveDocument.PrintPreview
ActiveDocument.ClosePrintPreview
Application.ScreenUpdating = True
End Sub

As coded, the macro will preserve a:
. bookmark applied to any cell value (eg for cross-referencing
purposes)
. field-coded formulae
. cross-references, and
leaves values formatted as percentages, dates & times alone.
Currency values and all other values are converted with a comma
for
a thousands separator and a period for a decimal separator. If you
have other currency forms, you can simply add them to the
'strCurr' variable. Decimals are to two places. Cross-references
within the table and the body of the document generally are
also
updated.

--
Cheers
macropod
[Microsoft MVP - Word]


I have a Word 2007 table with two columns that contain numbers. My
client
wants these numbers to reflect one decimal place. This document is
fielded to
serveral locations and I do not wish to involve Excel in
"importing a table".
There has to be a simple way to accomplish this. Maybe a macro?


.

.

 

[Lisa]

ahhh! Gotcha. Well then, I'd say things are working marvistically (marvelous
& fantastic)!

Thanks so much and if you talk to macropod please tell I said thank you,
again!

--
Lisa Stewart
Technical Writer
Yulista Management Services
Huntsville, AL

I believe that macropod has gone off on a road trip so he might not be back
here for a while.

I guess that you selected the 5 numbers in col 1 by using Ctrl+Left Click,
which does allow you to select non-contiguous ranges. However, a macro can
only work on a contiguous range and that is the reason that it only affects
the first of the selected cells.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Macropod - thanks. I've finally had the chance to run the macro with the
parameters I needed. You're great!

I do have one small ?: The below 3 columns respresent the columns I was
editing. Only the numbers with ".0" are the numbers I applied the macro
to.
My ? is - although I selected all the numbers in col 2 and 5 numbers in
col
1, the macro changed only 1 number in col 1. If I selected only the
numbers
in col 2 and ran the macro then it changed the numbers as it was intended.
Is
the macro running correctly?

WEIGHT ARM MOM/1000
8.0 176.0 1.4
7.8 230.0 1.8
2.2 176.0 0.4
2.5 203.0 0.5
3.0 358.0 1.1
1.0 176.0 0.2
0.3 176.0 0.1
2.3 203.0 0.5
1.0 212.0 0.2
0.9 212.0 0.2
0.3 225.0 0.1
1.4 331.0 0.5
12.0 176.0 2.1

--
Lisa Stewart
Technical Writer
Yulista Management Services
Huntsville, AL

Hi Lisa,

To make the macro work on only the selected cells, change:
..Tables(1).Range.Cells
to
..Cells

To make the macro work on all cells in all tables in the body of the
document,
1. add:
, oTbl As Table
to the line beginning with
Dim
2. change:
With Selection
'check that the selection is in a table
If .Information(wdWithInTable) = True Then
'check each cell in the table
For Each oCel In .Tables(1).Range.Cells
to:
With ActiveDocument
'loop through all tables
For Each oTbl In .Tables
'check each cell in the table
For Each oCel In oTbl.Range.Cells
3. and change:
.Fields.Update (or .Tables(1).Range.Fields.Update)
End if
End With
or:
.Tables(1).Range.Fields.Update
End if
End With
to:
.Fields.Update
Next
End With



--
Cheers
macropod
[Microsoft MVP - Word]


Hi Lisa,

Provided any part of a table is selected,the macro processes the whole
table.

--
Cheers
macropod
[Microsoft MVP - Word]


OK - I've finally had the chance to create the macro with the code you
provided. Before I run it, do I select the entire table, just the
columns
with numbers in them, or just the cells I want the macro to affect?

Thanks.
--
Lisa Stewart
Technical Writer
Yulista Management Services
Huntsville, AL



:

Hi Lisa,

You might get better results by changing .Fields.Update to
.Tables(1).Range.Fields.Update

--
Cheers
macropod
[Microsoft MVP - Word]


Hi Lisa,

There's no auto-formatting, so anything you do (short of using
formfields and protecting the document for forms) won't result
in
numbers that are input into the table being auto-formatted. With
that proviso, try the following macro:
Sub NumberFormatter()
Application.ScreenUpdating = False
Dim oCel As Cell, RngCel As Range, strBM As String, strChr, strCurr
As String, i As Integer
'currency symbols
strCurr = "$,€,£,¥"
With Selection
'check that the selection is in a table
If .Information(wdWithInTable) = True Then
'check each cell in the table
For Each oCel In .Tables(1).Range.Cells
'get each cell's contents, excluding the cell marker
Set RngCel = oCel.Range
RngCel.End = RngCel.End - 1
'check for are any fields in the cell
If RngCel.Fields.Count = 0 Then
'clear the temporary bookmark store
strBM = ""
' check for any bookmarks pertaining to the cell
If RngCel.Bookmarks.Count > 0 Then
'capture the first bookmark that exactly matches this cell
If RngCel.Bookmarks(1).Range = RngCel Then strBM =
RngCel.Bookmarks(1).Name
End If
'check that the cell contains a number - this includes
currency in a format
'matching the system's regional settings
If IsNumeric(RngCel.Text) = True Then
'skip over percentages
If Right(RngCel.Text, 1) <> "%" Then
'test whether the first character is the local currency
symbol
strChr = Left(RngCel.Text, 1)
If Not IsNumeric(strChr) Then
'currency symbol found, so reformat the value with
that symbol
RngCel.Text = strChr & Format(RngCel.Text, "#,##0.00")
Else
'currency symbol not found, so reformat the value
RngCel.Text = Format(RngCel.Text, "#,##0.00")
End If
End If
Else
'check for other currencies in cells that aren't
automatically assessed as values
For i = 0 To UBound(Split(strCurr, ","))
'test whether the first character is a currency symbol
If Left(RngCel.Text, 1) = Split(strCurr, ",")(i) Then
'store the cell text, minus the currency symbol
strChr = Replace(RngCel.Text, Split(strCurr, ",")(i),
"")
'check that the rest of the cell contains a number
If IsNumeric(strChr) = True Then
'currency symbol found, so reformat the value with
that symbol
RngCel.Text = Split(strCurr, ",")(i) &
Format(strChr, "#,##0.00")
End If
End If
Next
End If
'restore the bookmark if there was one
If strBM <> "" Then ActiveDocument.Bookmarks.Add strBM,
RngCel
End If
Next
'update any fields in the table
.Fields.Update
End If
End With
'update fields elsewhere in the body of the document
ActiveDocument.PrintPreview
ActiveDocument.ClosePrintPreview
Application.ScreenUpdating = True
End Sub

As coded, the macro will preserve a:
. bookmark applied to any cell value (eg for cross-referencing
purposes)
. field-coded formulae
. cross-references, and
leaves values formatted as percentages, dates & times alone.
Currency values and all other values are converted with a comma
for
a thousands separator and a period for a decimal separator. If you
have other currency forms, you can simply add them to the
'strCurr' variable. Decimals are to two places. Cross-references
within the table and the body of the document generally are
also
updated.

--
Cheers
macropod
[Microsoft MVP - Word]


I have a Word 2007 table with two columns that contain numbers. My
client
wants these numbers to reflect one decimal place. This document is
fielded to
serveral locations and I do not wish to involve Excel in
"importing a table".
There has to be a simple way to accomplish this. Maybe a macro?


.



.