Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting Text Numbers
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting Text Numbers
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 --- Regards, Norman "Don Wiss" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting Text Numbers
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" wrote in message ... 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.714 / Virus Database: 470 - Release Date: 2/07/2004 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting Text Numbers
On Fri, 9 Jul 2004 20:38:49 +1000, "macropod" wrote:
Don Wiss wrote: 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? 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting Text Numbers
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" wrote in message ... On Fri, 9 Jul 2004 20:38:49 +1000, "macropod" wrote: Don Wiss wrote: 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? 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.714 / Virus Database: 470 - Release Date: 2/07/2004 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting Text Numbers
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. On Sun, 11 Jul 2004 10:07:53 +1000, "macropod" wrote: 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" wrote in message .. . On Fri, 9 Jul 2004 20:38:49 +1000, "macropod" wrote: Don Wiss wrote: 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? 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.714 / Virus Database: 470 - Release Date: 2/07/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there way of detecting whether a cell is Bold text? | Excel Discussion (Misc queries) | |||
Detecting text and returning a value | Excel Discussion (Misc queries) | |||
Detecting Numbers in a cell | Excel Worksheet Functions | |||
Detecting Case of Text in a Cell | Excel Discussion (Misc queries) | |||
Detecting Blanks and Non Text Characters. | Excel Worksheet Functions |