Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format Issue
Cell format issue:
There are times when I run into an issue where I am typing in formulas and somewhere along the way the format changes. Example: Cell B2 has data/number 30 Cell B3 has data/number 50 Cell B4 has data/number 60 Cell B5 has data/number 70 Cell B6 has data/number 80 Cell C2 has a formula =B2 Cell C3 has a formula =B3 Cell C4 has a formula =B4 Cell C5 has a formula =B5 Cell C6 has a formula =B6 Results: Cell C2 shows a result of 30 Cell C3 shows a result of 50 Cell C4 shows a result of 60 Cell C5 shows a result of =B5 Cell C6 shows a result of =B6 My first question is What key board stroke am I doing to cause this unwanted result? (I must be bumping some key(s) to cause this issue because every cell updated after the issue starts repeats the same format issue.) My second question is, How do I change the cell to return numbers? 1. I have tried Format Cells, select Numbers, 2 decimal places and the okay button. Side Note: Despite selecting numbers in the catalog box... the Sample box always shows the formula rather than the desired number. Why did the format not change to numbers?) 2. I have also tried Tools/Options/ and under the window box verified the formula box does NOT have a check mark. (By the way I noticed that if I have selected cells without the format issue and check off the formula box the return is the formula with a LEFT alignment. Thus the cell cells are formatted as numbers where as the cells with the format issue still is text. Why? 3. The only thing that seems to work is that I go to another file and copy a cell that correctly returns numbers (The cell has to be one with a formula created prior to when issue starts) and paste it in cells that returns formula (with right alignment). This is not a good solution at times and I have had to contact IT who can not explain how to correct the issue so they reinstalling excel prior to when the issue arises. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format Issue
DS
The cells showing the formulas and not the results were most likely pre-formatted as text. Format to General then F2 then ENTER to re-enter the formulas. If a great bunch of these to be done use EditReplace what: = with: = Replace all. Gord Dibben MS Excel MVP On Thu, 12 Oct 2006 12:40:02 -0700, DS wrote: Cell format issue: There are times when I run into an issue where I am typing in formulas and somewhere along the way the format changes. Example: Cell B2 has data/number 30 Cell B3 has data/number 50 Cell B4 has data/number 60 Cell B5 has data/number 70 Cell B6 has data/number 80 Cell C2 has a formula =B2 Cell C3 has a formula =B3 Cell C4 has a formula =B4 Cell C5 has a formula =B5 Cell C6 has a formula =B6 Results: Cell C2 shows a result of 30 Cell C3 shows a result of 50 Cell C4 shows a result of 60 Cell C5 shows a result of =B5 Cell C6 shows a result of =B6 My first question is What key board stroke am I doing to cause this unwanted result? (I must be bumping some key(s) to cause this issue because every cell updated after the issue starts repeats the same format issue.) My second question is, How do I change the cell to return numbers? 1. I have tried Format Cells, select Numbers, 2 decimal places and the okay button. Side Note: Despite selecting numbers in the catalog box... the Sample box always shows the formula rather than the desired number. Why did the format not change to numbers?) 2. I have also tried Tools/Options/ and under the window box verified the formula box does NOT have a check mark. (By the way I noticed that if I have selected cells without the format issue and check off the formula box the return is the formula with a LEFT alignment. Thus the cell cells are formatted as numbers where as the cells with the format issue still is text. Why? 3. The only thing that seems to work is that I go to another file and copy a cell that correctly returns numbers (The cell has to be one with a formula created prior to when issue starts) and paste it in cells that returns formula (with right alignment). This is not a good solution at times and I have had to contact IT who can not explain how to correct the issue so they reinstalling excel prior to when the issue arises. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format Issue
Dear Gord Debben:
I will try your suggestion when I encounter the issue again. (I am unable to recreated issue it at this time.) Unanswered Question: Do you know why the format is changing at mid stream? Am I hitting short cut key by mistake? (Other staff members here have the same problem - random occurance.) Thank you, DS "Gord Dibben" wrote: DS The cells showing the formulas and not the results were most likely pre-formatted as text. Format to General then F2 then ENTER to re-enter the formulas. If a great bunch of these to be done use EditReplace what: = with: = Replace all. Gord Dibben MS Excel MVP On Thu, 12 Oct 2006 12:40:02 -0700, DS wrote: Cell format issue: There are times when I run into an issue where I am typing in formulas and somewhere along the way the format changes. Example: Cell B2 has data/number 30 Cell B3 has data/number 50 Cell B4 has data/number 60 Cell B5 has data/number 70 Cell B6 has data/number 80 Cell C2 has a formula =B2 Cell C3 has a formula =B3 Cell C4 has a formula =B4 Cell C5 has a formula =B5 Cell C6 has a formula =B6 Results: Cell C2 shows a result of 30 Cell C3 shows a result of 50 Cell C4 shows a result of 60 Cell C5 shows a result of =B5 Cell C6 shows a result of =B6 My first question is What key board stroke am I doing to cause this unwanted result? (I must be bumping some key(s) to cause this issue because every cell updated after the issue starts repeats the same format issue.) My second question is, How do I change the cell to return numbers? 1. I have tried Format Cells, select Numbers, 2 decimal places and the okay button. Side Note: Despite selecting numbers in the catalog box... the Sample box always shows the formula rather than the desired number. Why did the format not change to numbers?) 2. I have also tried Tools/Options/ and under the window box verified the formula box does NOT have a check mark. (By the way I noticed that if I have selected cells without the format issue and check off the formula box the return is the formula with a LEFT alignment. Thus the cell cells are formatted as numbers where as the cells with the format issue still is text. Why? 3. The only thing that seems to work is that I go to another file and copy a cell that correctly returns numbers (The cell has to be one with a formula created prior to when issue starts) and paste it in cells that returns formula (with right alignment). This is not a good solution at times and I have had to contact IT who can not explain how to correct the issue so they reinstalling excel prior to when the issue arises. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format Issue
Great I will try you solution the next time the issue occures ( At the
current time I can not recreate the issue.) By the way can you tell me why the format changed at mid stream of data entry? (Other co-workers have the same issue and we are wondering if we are hitting a short cut key or something by mistake.) DS "Gord Dibben" wrote: DS The cells showing the formulas and not the results were most likely pre-formatted as text. Format to General then F2 then ENTER to re-enter the formulas. If a great bunch of these to be done use EditReplace what: = with: = Replace all. Gord Dibben MS Excel MVP On Thu, 12 Oct 2006 12:40:02 -0700, DS wrote: Cell format issue: There are times when I run into an issue where I am typing in formulas and somewhere along the way the format changes. Example: Cell B2 has data/number 30 Cell B3 has data/number 50 Cell B4 has data/number 60 Cell B5 has data/number 70 Cell B6 has data/number 80 Cell C2 has a formula =B2 Cell C3 has a formula =B3 Cell C4 has a formula =B4 Cell C5 has a formula =B5 Cell C6 has a formula =B6 Results: Cell C2 shows a result of 30 Cell C3 shows a result of 50 Cell C4 shows a result of 60 Cell C5 shows a result of =B5 Cell C6 shows a result of =B6 My first question is What key board stroke am I doing to cause this unwanted result? (I must be bumping some key(s) to cause this issue because every cell updated after the issue starts repeats the same format issue.) My second question is, How do I change the cell to return numbers? 1. I have tried Format Cells, select Numbers, 2 decimal places and the okay button. Side Note: Despite selecting numbers in the catalog box... the Sample box always shows the formula rather than the desired number. Why did the format not change to numbers?) 2. I have also tried Tools/Options/ and under the window box verified the formula box does NOT have a check mark. (By the way I noticed that if I have selected cells without the format issue and check off the formula box the return is the formula with a LEFT alignment. Thus the cell cells are formatted as numbers where as the cells with the format issue still is text. Why? 3. The only thing that seems to work is that I go to another file and copy a cell that correctly returns numbers (The cell has to be one with a formula created prior to when issue starts) and paste it in cells that returns formula (with right alignment). This is not a good solution at times and I have had to contact IT who can not explain how to correct the issue so they reinstalling excel prior to when the issue arises. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format Issue
I don't know of a shortcut to format cells as text.
There are keyboard shortcuts to format as Date, Time, Number, Percentage, General, Currency.......but not Text. Were those cells used for something else before you started entering formulas? The text format may be left over from earlier data input. Gord On Thu, 12 Oct 2006 13:59:02 -0700, DS wrote: Dear Gord Debben: I will try your suggestion when I encounter the issue again. (I am unable to recreated issue it at this time.) Unanswered Question: Do you know why the format is changing at mid stream? Am I hitting short cut key by mistake? (Other staff members here have the same problem - random occurance.) Thank you, DS "Gord Dibben" wrote: DS The cells showing the formulas and not the results were most likely pre-formatted as text. Format to General then F2 then ENTER to re-enter the formulas. If a great bunch of these to be done use EditReplace what: = with: = Replace all. Gord Dibben MS Excel MVP On Thu, 12 Oct 2006 12:40:02 -0700, DS wrote: Cell format issue: There are times when I run into an issue where I am typing in formulas and somewhere along the way the format changes. Example: Cell B2 has data/number 30 Cell B3 has data/number 50 Cell B4 has data/number 60 Cell B5 has data/number 70 Cell B6 has data/number 80 Cell C2 has a formula =B2 Cell C3 has a formula =B3 Cell C4 has a formula =B4 Cell C5 has a formula =B5 Cell C6 has a formula =B6 Results: Cell C2 shows a result of 30 Cell C3 shows a result of 50 Cell C4 shows a result of 60 Cell C5 shows a result of =B5 Cell C6 shows a result of =B6 My first question is What key board stroke am I doing to cause this unwanted result? (I must be bumping some key(s) to cause this issue because every cell updated after the issue starts repeats the same format issue.) My second question is, How do I change the cell to return numbers? 1. I have tried Format Cells, select Numbers, 2 decimal places and the okay button. Side Note: Despite selecting numbers in the catalog box... the Sample box always shows the formula rather than the desired number. Why did the format not change to numbers?) 2. I have also tried Tools/Options/ and under the window box verified the formula box does NOT have a check mark. (By the way I noticed that if I have selected cells without the format issue and check off the formula box the return is the formula with a LEFT alignment. Thus the cell cells are formatted as numbers where as the cells with the format issue still is text. Why? 3. The only thing that seems to work is that I go to another file and copy a cell that correctly returns numbers (The cell has to be one with a formula created prior to when issue starts) and paste it in cells that returns formula (with right alignment). This is not a good solution at times and I have had to contact IT who can not explain how to correct the issue so they reinstalling excel prior to when the issue arises. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format cells (number) issue | Excel Discussion (Misc queries) | |||
# format issue | Excel Discussion (Misc queries) | |||
DATE FORMAT issue | Excel Worksheet Functions | |||
Upper Case and date format issue | Excel Discussion (Misc queries) | |||
concatenate number format issue | Excel Worksheet Functions |