| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
a code to trace pure numbers formatted as Text
Dear All,
in a world full of bugs... and in worksheets programmed to function as required... Can someone please avail a code/macro that can highlight a cell<s which contains pure numbers yet are formatted as "Text". I need to assure that even when there comes a bug to change the cell from any formatted number into a text, i can still review easily and verify the worksheet to assure integrity on my logical-numeric based- formulas. TIA |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
a code to trace pure numbers formatted as Text
I'm not sure what pure numbers are, but this may work in most cases.
But it does assume that you're not using format|Conditional formatting for anything else. Select your range (I used A1:x99). With the activecell A1, use Format|Conditional formatting formula is: =AND(ISNUMBER(-A1),ISTEXT(A1)) and give it a nice shade. Be aware that these will look like they can be numbers to excel: '1E3 'January 1, 2007 '00:12:30 1E3 = 1*10^3 = 1000 and dates and times are numbers to excel. swordsman wrote: Dear All, in a world full of bugs... and in worksheets programmed to function as required... Can someone please avail a code/macro that can highlight a cell<s which contains pure numbers yet are formatted as "Text". I need to assure that even when there comes a bug to change the cell from any formatted number into a text, i can still review easily and verify the worksheet to assure integrity on my logical-numeric based- formulas. TIA -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
a code to trace pure numbers formatted as Text
that's a treat,
I tried it on two cells, it does provide shining colors for numbers formatted as text. Problem is, upon knowing through the colors, when I change these cells from Text to General, the cells do not update even when I close/save and open the file... It looks like it is a must for me to press always f2...on each cells <with pure numbers in order that my excel recognize the change in the format and *formula*... Is there any other way we can automate this action <change format from text to general while the logical formulas <in the cell or other cells linked here and in the cells Cond. Format automate itself as well ? Cheers! "Dave Peterson" wrote: I'm not sure what pure numbers are, but this may work in most cases. But it does assume that you're not using format|Conditional formatting for anything else. Select your range (I used A1:x99). With the activecell A1, use Format|Conditional formatting formula is: =AND(ISNUMBER(-A1),ISTEXT(A1)) and give it a nice shade. Be aware that these will look like they can be numbers to excel: '1E3 'January 1, 2007 '00:12:30 1E3 = 1*10^3 = 1000 and dates and times are numbers to excel. swordsman wrote: Dear All, in a world full of bugs... and in worksheets programmed to function as required... Can someone please avail a code/macro that can highlight a cell<s which contains pure numbers yet are formatted as "Text". I need to assure that even when there comes a bug to change the cell from any formatted number into a text, i can still review easily and verify the worksheet to assure integrity on my logical-numeric based- formulas. TIA -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
a code to trace pure numbers formatted as Text
Changing the format doesn't change the value of the cell.
Format the cells as General (or anything but text) One quick way to change text numbers to number numbers is to select an empty cell. Edit|copy that cell select the range to fix edit|paste special|check add and values swordsman wrote: that's a treat, I tried it on two cells, it does provide shining colors for numbers formatted as text. Problem is, upon knowing through the colors, when I change these cells from Text to General, the cells do not update even when I close/save and open the file... It looks like it is a must for me to press always f2...on each cells <with pure numbers in order that my excel recognize the change in the format and *formula*... Is there any other way we can automate this action <change format from text to general while the logical formulas <in the cell or other cells linked here and in the cells Cond. Format automate itself as well ? Cheers! "Dave Peterson" wrote: I'm not sure what pure numbers are, but this may work in most cases. But it does assume that you're not using format|Conditional formatting for anything else. Select your range (I used A1:x99). With the activecell A1, use Format|Conditional formatting formula is: =AND(ISNUMBER(-A1),ISTEXT(A1)) and give it a nice shade. Be aware that these will look like they can be numbers to excel: '1E3 'January 1, 2007 '00:12:30 1E3 = 1*10^3 = 1000 and dates and times are numbers to excel. swordsman wrote: Dear All, in a world full of bugs... and in worksheets programmed to function as required... Can someone please avail a code/macro that can highlight a cell<s which contains pure numbers yet are formatted as "Text". I need to assure that even when there comes a bug to change the cell from any formatted number into a text, i can still review easily and verify the worksheet to assure integrity on my logical-numeric based- formulas. TIA -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
a code to trace pure numbers formatted as Text
thanks Dave,
I will wait until someone can shorten the complete trick by a macro for a multiple sheets workbook. best wishes "Dave Peterson" wrote: Changing the format doesn't change the value of the cell. Format the cells as General (or anything but text) One quick way to change text numbers to number numbers is to select an empty cell. Edit|copy that cell select the range to fix edit|paste special|check add and values swordsman wrote: that's a treat, I tried it on two cells, it does provide shining colors for numbers formatted as text. Problem is, upon knowing through the colors, when I change these cells from Text to General, the cells do not update even when I close/save and open the file... It looks like it is a must for me to press always f2...on each cells <with pure numbers in order that my excel recognize the change in the format and *formula*... Is there any other way we can automate this action <change format from text to general while the logical formulas <in the cell or other cells linked here and in the cells Cond. Format automate itself as well ? Cheers! "Dave Peterson" wrote: I'm not sure what pure numbers are, but this may work in most cases. But it does assume that you're not using format|Conditional formatting for anything else. Select your range (I used A1:x99). With the activecell A1, use Format|Conditional formatting formula is: =AND(ISNUMBER(-A1),ISTEXT(A1)) and give it a nice shade. Be aware that these will look like they can be numbers to excel: '1E3 'January 1, 2007 '00:12:30 1E3 = 1*10^3 = 1000 and dates and times are numbers to excel. swordsman wrote: Dear All, in a world full of bugs... and in worksheets programmed to function as required... Can someone please avail a code/macro that can highlight a cell<s which contains pure numbers yet are formatted as "Text". I need to assure that even when there comes a bug to change the cell from any formatted number into a text, i can still review easily and verify the worksheet to assure integrity on my logical-numeric based- formulas. TIA -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| numbers formatted as text | Excel Discussion (Misc queries) | |||
| Pivot Table keep apart cells text that I've formatted as numbers | Excel Worksheet Functions | |||
| Converting numbers formatted as text to numbers | Excel Discussion (Misc queries) | |||
| Recognising Text formatted cells in code | Excel Programming | |||
| Formatting a cell in Excel, using pure VB code | Excel Programming | |||
Hybrid Mode
