On Mar 11, 1:31 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
anyText = anyCell.Text

Yes, that is the key. Thanks. That was what I thought I should use,
but I misread the Help page and did not even test my intuition. Argh!

By the way, below is my final version. It worked in a blink of an eye
-- although efficiency was never my concern. My rendition is clearly
simpler than yours. Perhaps you could offer a critical review to
explain any oversights due to my simplification.

Thanks again. Your guidance is much appreciated.

It fairly well preserves the format and layout except when the Accounting
format has been chosen (that puts leading spaces between the $ and 1st digit

My rendition of the macro has no trouble preserving the Accounting
spacing without extra effort. I am using Office Excel 2003.

PS: I am a little surprised that the macro works for negative
numbers. All of my cells are formatted as Number with comma separator
and some number of decimal places (varies). When I manually type -
xxx,xxx into a cell, I get a #NAME! error. I have to prefix it with
an apostrophe. Yet the macro has no problem changing -123,456 to -
xxx,xxx. Go figure!


Sub Xover()
Dim cell As Object
Dim val As String
Dim i As Integer
Dim n As String
Application.ScreenUpdating = False
For Each cell In Selection
If IsNumeric(cell.Value) Then
val = cell.Text
For i = 1 To Len(val)
n = Mid(val, i, 1)
If "0" <= n And n <= "9" Then
Mid(val, i, 1) = "x"
End If
Next
cell.Formula = val
End If
Next
Application.ScreenUpdating = True
End Sub