Word VBA format number in a Table cell - cell text unrecognized

Question

I'm constructing a Word doc from VBA (Excel) with Excel table data. The data was placed in the table OK. The last column has numbers (e.g. 456789) That I want to number-format (e.g. $456,789).

Word adds chr(13) and chr(7) at the end of each table cell value (so that the length of the visible value 456789 is not 6 but 8).

Without the Replace statement below to eliminate these two chars I could not treat the value as a string and further manipulate.

Is this the best way vba has to offer for accessing table cells values as a standard type such as String? Why do I need to eliminate the ending chars in the process? what am I missing?

        For Each cell In .Columns(3).Cells
            strCellValue = CStr(cell.Range.Text)
            strCellValue = Replace(strCellValue, Chr(13) & Chr(7), "")
            If IsNumeric(strCellValue) Then
                cell.Range.Text = Format(strCellValue, "$#,##0")
            End If
        Next

Thanks!

Your question isn't clear... Everything in Word is a string - it supports no other data type. The two characters at the end of a table cell are, in combination, the cell structure and are always at the end of a table cell. So the always need to be stripped off, one way or another. Personally, I cut off the last character(s); see stackoverflow.com/questions/55128350/… — Cindy Meister
Thanks Cindy. Yes, see my comment to macropod's answer here. — Mor Sagmon

macropod macropod · Accepted Answer · 2019-04-30T22:09:19

There are numerous ways of doing this. Perhaps the simplest is:

For Each Cell In .Columns(3).Cells
    strCellValue = Split(Cell.Range.Text, vbCr)(0)
    If IsNumeric(strCellValue) Then Cell.Range.Text = Format(strCellValue, "$#,##0")
Next

Word VBA format number in a Table cell - cell text unrecognized

1 Answers