Cell formatting behaviour question
 

Hey guys,
I have 2 related cases that I dont understand how to get Excel formatting to
work:

1. If I imported or copied rows of data (numerical) from another file (Word
or Access) into Excel, the data is presented as raw numbers in each cell.
Now I apply a formatting (i.e. I want comma separation for thousands, etc.)
and it will not show up. However, if i then double-click inside a cell (as
if to edit the contents directly), then hit enter the formatting I want
shows up... but only that cell. I have hundreds of rows and I really need to
force excel to display the formatting i want immediately. I.e. if i select
number format, with 2 decimal and comma separation, i want excel to display
that way.

2. Very similar case... sometimes if I import from Access, numbers that are
long, excel will display as 1E09 etc. I want to display the full number, but
it only works when i double-click to edit contents of the cell and then hit
enter.

I have tried manipulations, including copy values only to another column and
then apply the formatting fresh, etc. Doesn't work.

Very frustrating!!

Any help appreciated, thanks

Derek

Try this.

Format the cells to Number or General then copy an empty cell.

Paste SpecialVlauesAddOKEsc.

Your numbers should now be real numbers and can be formatted as you like.

Has to be done after the import/copy unless you use some sort of event code
when importing/copying to the sheet.


Gord Dibben Excel MVP

On Tue, 22 Feb 2005 11:32:49 -0500, "derek" wrote:

Hey guys,
I have 2 related cases that I dont understand how to get Excel formatting to
work:

1. If I imported or copied rows of data (numerical) from another file (Word
or Access) into Excel, the data is presented as raw numbers in each cell.
Now I apply a formatting (i.e. I want comma separation for thousands, etc.)
and it will not show up. However, if i then double-click inside a cell (as
if to edit the contents directly), then hit enter the formatting I want
shows up... but only that cell. I have hundreds of rows and I really need to
force excel to display the formatting i want immediately. I.e. if i select
number format, with 2 decimal and comma separation, i want excel to display
that way.

2. Very similar case... sometimes if I import from Access, numbers that are
long, excel will display as 1E09 etc. I want to display the full number, but
it only works when i double-click to edit contents of the cell and then hit
enter.

I have tried manipulations, including copy values only to another column and
then apply the formatting fresh, etc. Doesn't work.

Very frustrating!!

Any help appreciated, thanks

Thanks Gord

A friend also just found a similar way, he went to a new column and did "=A1
+ 0" for example. The new cells were properly formatted, then he pasted the
values back where i wanted them

Seems pretty silly, but it works, thanks!


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Derek

Try this.

Format the cells to Number or General then copy an empty cell.

Paste SpecialVlauesAddOKEsc.

Your numbers should now be real numbers and can be formatted as you like.

Has to be done after the import/copy unless you use some sort of event
code
when importing/copying to the sheet.


Gord Dibben Excel MVP

On Tue, 22 Feb 2005 11:32:49 -0500, "derek" wrote:

Hey guys,
I have 2 related cases that I dont understand how to get Excel formatting
to
work:

1. If I imported or copied rows of data (numerical) from another file
(Word
or Access) into Excel, the data is presented as raw numbers in each cell.
Now I apply a formatting (i.e. I want comma separation for thousands,
etc.)
and it will not show up. However, if i then double-click inside a cell
(as
if to edit the contents directly), then hit enter the formatting I want
shows up... but only that cell. I have hundreds of rows and I really need
to
force excel to display the formatting i want immediately. I.e. if i
select
number format, with 2 decimal and comma separation, i want excel to
display
that way.

2. Very similar case... sometimes if I import from Access, numbers that
are
long, excel will display as 1E09 etc. I want to display the full number,
but
it only works when i double-click to edit contents of the cell and then
hit
enter.

I have tried manipulations, including copy values only to another column
and
then apply the formatting fresh, etc. Doesn't work.

Very frustrating!!

Any help appreciated, thanks

And a lot more more work that my method<g


Gord

On Tue, 22 Feb 2005 14:37:35 -0500, "derek" wrote:


Thanks Gord

A friend also just found a similar way, he went to a new column and did "=A1
+ 0" for example. The new cells were properly formatted, then he pasted the
values back where i wanted them

Seems pretty silly, but it works, thanks!


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Derek

Try this.

Format the cells to Number or General then copy an empty cell.

Paste SpecialVlauesAddOKEsc.

Your numbers should now be real numbers and can be formatted as you like.

Has to be done after the import/copy unless you use some sort of event
code
when importing/copying to the sheet.


Gord Dibben Excel MVP

On Tue, 22 Feb 2005 11:32:49 -0500, "derek" wrote:

Hey guys,
I have 2 related cases that I dont understand how to get Excel formatting
to
work:

1. If I imported or copied rows of data (numerical) from another file
(Word
or Access) into Excel, the data is presented as raw numbers in each cell.
Now I apply a formatting (i.e. I want comma separation for thousands,
etc.)
and it will not show up. However, if i then double-click inside a cell
(as
if to edit the contents directly), then hit enter the formatting I want
shows up... but only that cell. I have hundreds of rows and I really need
to
force excel to display the formatting i want immediately. I.e. if i
select
number format, with 2 decimal and comma separation, i want excel to
display
that way.

2. Very similar case... sometimes if I import from Access, numbers that
are
long, excel will display as 1E09 etc. I want to display the full number,
but
it only works when i double-click to edit contents of the cell and then
hit
enter.

I have tried manipulations, including copy values only to another column
and
then apply the formatting fresh, etc. Doesn't work.

Very frustrating!!

Any help appreciated, thanks