On Mar 27, 1:18 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
this may be completely off the wall, but it semed to work in my "very limited"
testing. it may be flawed, but give it a try.
just paste in on the code for the sheet in question, not a general module.

Option Explicit
Public onf As String
Public nnf As String
Private Sub Worksheet_Change(ByVal Target As Range)
nnf = Target.NumberFormat
Debug.Print nnf
If onf = nnf Then
Exit Sub
Else
ActiveCell.NumberFormat = onf
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
onf = ActiveCell.NumberFormat
Debug.Print onf
End Sub


Thanks, I tried that but all it does is store the destination number
formatting, and reapply it after the damage is done. Leading and
trailing zeros are lost, and exponential notation is still there. For
example:

In your worksheet with your code behind it, copy the text
00123456789876543210000 (including leading and trailing zeros) from
some other rich-text application such as email or Word, and paste into
Excel in a cell with text formatting. Yes, the cell gets its text
formatting back, but the damage is already done -- what happened to
the leading and trailing zeros? How do I find out how many leading and
trailing zeros there were, so I can replace them?


Thanks,

Greg