Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to convert german format numbers to british format boysie Excel Discussion (Misc queries) 3 March 8th 08 07:27 PM
Convert numbers from text format to number format merlin68 Excel Discussion (Misc queries) 7 June 20th 07 07:03 PM
How do i change numbers in text format to number format? Greg New Users to Excel 1 December 14th 04 06:22 PM


All times are GMT +1. The time now is 09:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"