Find text numbers

[Jan]

How do I find numbers formated as text in a sheet?
Need help for vba as well as without vba.

Thanks
Jan

 

[Sharad Naik]

Through VBA code:
The below code will find the cells having numbers but formatted as text, in
Sheet1 and will add comment to such cells.
It is assumed that none of such cells already have a comment.
Sub TextNumber()
For Each cell In Sheet1.UsedRange
If IsNumeric(cell.Value) And cell.NumberFormat = "@" Then
cell.AddComment "Text Number!"
cell.Comment.Shape.Height = 12
cell.Comment.Shape.Width = 55
cell.Comment.Visible = True
End If
Next cell
End Sub

Without VBA: I don't understand this fully. But following will help.
By default if cell has a numeric data and is formatted either as General or
Number,
the cell horizontal alignment is right.
If it is formatted as text, the alignment is left.
So by just looking at the cells you can determine, if the cell contains a
number and
is aligned left, the cell is formatted as text.
This is assuming that the user has not change the default horizontal
alignment, either to right for the
text formatted cells or to left for the general or number formatted cells.

Otherwise you have to see the cell properties for each cells conataining
numeric data.

Sharad