Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format numbers problem
I consider myself very proficient in Excel so this problem is rather
perplexing. I am entering numbers into cells in the following format: 63,98,99 64,100,101 65,102,103 I am also color coding the numbers, the first one being red, the last two being blue. These numbers are entered into four merged cells each: AR24:AS25 AT24:AU25 AV24:AW25 The first set of cells allow the formatting without any problem. The second two will not. This is what they will do. After I enter them, the comma will be automatically removed and the cell formatting with automatically revert to number instead of general. Any time I try to re-input the commas or change the cell format the formatting will revert back. I managed at one point to get the formatting to allow the commas by expanding the merged cell range but then it would not allow me to format the font color. I have done everything I can think of from copy-paste the format from the working cells to deleting the cells and adding new ones. Nothing has worked. Any help you can offer is appreciated. I am working on Excel 2007. One other curious oddity. There are no hyper links in these cells but the misbehaving cells will show the correct formatting (commas included, not the color) when I do a mouse over on the cells. I don't know how this is but I hope it can help diagnose the issue. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format numbers problem
On Fri, 28 Mar 2008 05:26:00 -0700, brodiemac
wrote: I consider myself very proficient in Excel so this problem is rather perplexing. I am entering numbers into cells in the following format: 63,98,99 64,100,101 65,102,103 I am also color coding the numbers, the first one being red, the last two being blue. These numbers are entered into four merged cells each: AR24:AS25 AT24:AU25 AV24:AW25 The first set of cells allow the formatting without any problem. The second two will not. This is what they will do. After I enter them, the comma will be automatically removed and the cell formatting with automatically revert to number instead of general. Any time I try to re-input the commas or change the cell format the formatting will revert back. I managed at one point to get the formatting to allow the commas by expanding the merged cell range but then it would not allow me to format the font color. I have done everything I can think of from copy-paste the format from the working cells to deleting the cells and adding new ones. Nothing has worked. Any help you can offer is appreciated. I am working on Excel 2007. One other curious oddity. There are no hyper links in these cells but the misbehaving cells will show the correct formatting (commas included, not the color) when I do a mouse over on the cells. I don't know how this is but I hope it can help diagnose the issue. Thanks. Here is what might be happening: Your first entry is interpreted as TEXT by Excel, so it behaves as you expect. Your second and third entries are interpreted as NUMBERS by Excel (I assume <comma is your thousands separator), so they get changed to the numeric values. Formatted as General, there would be no commas. Formatted as Number, the commas are retained. If you must have these entries formatted as General, and you want to see the commas, you will need to precede the entry with a single quote, so that Excel will interpret the value as Text, and not a number. --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format numbers problem
To Excel, the first set is a string, the others are numbers. Format the cells as text, or enter
your number strings with a leading single quote, and Excel will treat them all as strings. HTH, Bernie MS Excel MVP "brodiemac" wrote in message ... I consider myself very proficient in Excel so this problem is rather perplexing. I am entering numbers into cells in the following format: 63,98,99 64,100,101 65,102,103 I am also color coding the numbers, the first one being red, the last two being blue. These numbers are entered into four merged cells each: AR24:AS25 AT24:AU25 AV24:AW25 The first set of cells allow the formatting without any problem. The second two will not. This is what they will do. After I enter them, the comma will be automatically removed and the cell formatting with automatically revert to number instead of general. Any time I try to re-input the commas or change the cell format the formatting will revert back. I managed at one point to get the formatting to allow the commas by expanding the merged cell range but then it would not allow me to format the font color. I have done everything I can think of from copy-paste the format from the working cells to deleting the cells and adding new ones. Nothing has worked. Any help you can offer is appreciated. I am working on Excel 2007. One other curious oddity. There are no hyper links in these cells but the misbehaving cells will show the correct formatting (commas included, not the color) when I do a mouse over on the cells. I don't know how this is but I hope it can help diagnose the issue. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format numbers problem
Well I'll be........
That was it exactly. THANKS!!!! "Ron Rosenfeld" wrote: On Fri, 28 Mar 2008 05:26:00 -0700, brodiemac wrote: I consider myself very proficient in Excel so this problem is rather perplexing. I am entering numbers into cells in the following format: 63,98,99 64,100,101 65,102,103 I am also color coding the numbers, the first one being red, the last two being blue. These numbers are entered into four merged cells each: AR24:AS25 AT24:AU25 AV24:AW25 The first set of cells allow the formatting without any problem. The second two will not. This is what they will do. After I enter them, the comma will be automatically removed and the cell formatting with automatically revert to number instead of general. Any time I try to re-input the commas or change the cell format the formatting will revert back. I managed at one point to get the formatting to allow the commas by expanding the merged cell range but then it would not allow me to format the font color. I have done everything I can think of from copy-paste the format from the working cells to deleting the cells and adding new ones. Nothing has worked. Any help you can offer is appreciated. I am working on Excel 2007. One other curious oddity. There are no hyper links in these cells but the misbehaving cells will show the correct formatting (commas included, not the color) when I do a mouse over on the cells. I don't know how this is but I hope it can help diagnose the issue. Thanks. Here is what might be happening: Your first entry is interpreted as TEXT by Excel, so it behaves as you expect. Your second and third entries are interpreted as NUMBERS by Excel (I assume <comma is your thousands separator), so they get changed to the numeric values. Formatted as General, there would be no commas. Formatted as Number, the commas are retained. If you must have these entries formatted as General, and you want to see the commas, you will need to precede the entry with a single quote, so that Excel will interpret the value as Text, and not a number. --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format numbers problem
On Fri, 28 Mar 2008 06:05:01 -0700, brodiemac
wrote: Well I'll be........ That was it exactly. THANKS!!!! You're welcome. Glad to help. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert german format numbers to british format | Excel Discussion (Misc queries) | |||
Convert numbers from text format to number format | Excel Discussion (Misc queries) | |||
How do i change numbers in text format to number format? | New Users to Excel |