Detecting Text Numbers

[Don Wiss]

Some of our users are getting data from clients and pasting the data into
the spreadsheet. Sometimes the years are text. Even though the cell is
formatted as a number, being text it has a value of 0 to any cell that
references it. If I use a macro to test its value it gives me a number. If
I can find a way to detect this situation I can issue a
Range("YearColumn").Value = Range("YearColumn").Value
when deactivating the sheet. Or maybe just always do this when
deactivating?

Don <donwiss at panix.com>.

 

[Norman Jones]

Hi Don,

If I can find a way to detect this situation

One way would be to test the range object returned by the SpecialCells
method. Something like:

Sub Tester()
Dim rng As Range, cell As Range

Set rng = Nothing
On Error Resume Next
Set rng = Range("YearColumn").SpecialCells(xlConstants, 2)
On Error GoTo 0

If Not rng Is Nothing Then
For Each cell In rng
cell.value = --cell.value
Next
Else
'do nothing
End If

End Sub

 

[macropod]

Hi Don,
Visually, if the column is formatted as general, 'text' dates will be left
aligned, while 'numeric' dates will be right aligned. Making sure all dates
are numeric, is as simple as multiplying them by 1. This will convert the
'text' dates to 'numeric' date equivalents, though you may lose the cell's
date formatting in the process if you do it in the cell.

Cheers

 

[Don Wiss]

Visually, if the column is formatted as general, 'text' dates will be left
aligned, while 'numeric' dates will be right aligned.

But we have formatted the column as centered.

Making sure all dates
are numeric, is as simple as multiplying them by 1. This will convert the
'text' dates to 'numeric' date equivalents, though you may lose the cell's
date formatting in the process if you do it in the cell.

This will not work. As the dates are text they have a numeric value of 0.
Multiplying anything against them leaves their value at 0.

What I have done is every time the sheet is deactivated I simply run this
line: Range("YearColumn").Value = Range("YearColumn").Value

It works fine. While there are some on screen numbers that don't appear
while still on the sheet, all critical numbers are on later sheets, and
running this line makes them all correct. And when they return, the
calculated numbers on the input sheet will then appear.

Don <donwiss at panix.com>.

 

[macropod]

Hi Don,

You asked how to identify the cells. Changing the alignment temporarily as
suggested is one way to do that. Another way is via the ISNUMBER worksheet
function. A 'text' date will return FALSE.

You obviously didn't try multiplying the 'text' dates by 1.

Type 1 in any cell.
Copy the 1 to the clipboard.
Select a 'text' date.
Choose Edit|Paste Special|Multiply.
You should now have a serial number displayed. This is the number in Excel
that corresponds to your date.
Format this cell in your preferred date format and you will see your date
again - this time stored as a number.

 

[Don Wiss]

You are making a lot of discussion about dates. These are not dates but
years. Which if numbers they are integers. But being text they look like a
year, but any cell multiplication treats them as zero. And I certainly am
not going to expect my users to use any clipboard and paste special to
solve a problem they can't even see or detect.