Format numbers problem

[brodiemac]

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.

 

[Ron Rosenfeld]

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

 

[Bernie Deitrick]

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]

Well I'll be........

That was it exactly. THANKS!!!!

 

[Ron Rosenfeld]

Well I'll be........

That was it exactly. THANKS!!!!

You're welcome. Glad to help.
--ron