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


0
none89 (807)
2/22/2005 4:32:49 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
480 Views

Similar Articles

[PageSpeed] 1

Derek

Try this.

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

Paste Special>Vlaues>Add>OK>Esc.

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" <none@none.com> 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
>

0
Gord
2/22/2005 6:15:29 PM
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
news:7gtm11du6k5c4hrieqpm760r85akedjvk2@4ax.com...
> Derek
>
> Try this.
>
> Format the cells to Number or General then copy an empty cell.
>
> Paste Special>Vlaues>Add>OK>Esc.
>
> 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" <none@none.com> 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
> >
>


0
none89 (807)
2/22/2005 7:37:35 PM
And a lot more more work that my method<g>


Gord

On Tue, 22 Feb 2005 14:37:35 -0500, "derek" <none@none.com> 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
>news:7gtm11du6k5c4hrieqpm760r85akedjvk2@4ax.com...
>> Derek
>>
>> Try this.
>>
>> Format the cells to Number or General then copy an empty cell.
>>
>> Paste Special>Vlaues>Add>OK>Esc.
>>
>> 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" <none@none.com> 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
>> >
>>
>

0
Gord
2/22/2005 10:17:07 PM
Reply:

Similar Artilces:

newbie chart question #3
Thanks for the advice - is it possible to get it to draw a 'best-fit line over the points -- retrieve ----------------------------------------------------------------------- retriever's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1643 View this thread: http://www.excelforum.com/showthread.php?threadid=27796 From the Chart menu: Add a Trendline. You have several options of the type of fitted line, and how to format it. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ ______...

View and Cell Sum Range
I am looking for a way to change the view i.e. change hidden columns representing months and change the cell range sum within a totals column depending on which view (month) is selected. I am able to change the view by month but the cell range in the totals column does not change with custom view feature. Is there anyway to do this at one time without having to create a new file for each month? Thank you. -- Dewayne Here is one I just did for a client that should give you the idea. This one hides all days that are not the day of the date in the target cell. Private Sub Worksheet_Chan...

Turning Off Cell Border Double-Click
When I double-click to select or edit the contents of a cell, the worksheet jumps to either the top or bottom of the worksheet (cell border double-clicked). How do I turn this "feature" off? That's a HANDY feature if you use it right. That feature will take you to the last cell of a continuous range (same as CTRL-rt arrow or CTRL-dn arrow). It's not invoked unless you double click on the border of the cell so, if you disable, my guess is that NOTHING would happen since you didn't click IN the cell. Best way to avoid the result you don't like is to ensure ...

Quick Question 03-31-10
I know RPC over HTTP should be just that OVER http. Can someone confirm for me that the only ports I need open at my firewall will be 80/443 (http/https) Environment 2003sp2 environment On Wed, 31 Mar 2010 09:16:45 -0400, "Nik" <test> wrote: >I know RPC over HTTP should be just that OVER http. >Can someone confirm for me that the only ports I need open at my firewall >will be 80/443 (http/https) >Environment 2003sp2 environment 443 is all you need. You should not even bother with 80. Hi Nik: Yes, on your EXTERNAL firewall, you only need http...

Date format | Month/Year only changes to Day/Month/CurrentYear
Hi Y'all,I have a table field which I've formatted to MM/YY. On a form theinput box for that field is formatted the same. However if I enter adate such as 03/08 (March 2008) it becomes 08/07. Access sees 03/08 as3rd August and adds the current year. The data stored is 03/08/07.How can I stop this happening? I assume I can just use a formattednumber field but surely there's a way to keep the date format?Thanks in advance!Paul Paul wrote:> Hi Y'all,>> I have a table field which I've formatted to MM/YY. On a form the> input box for that field is formatted the same....

merg document formatting
I have a word document (catalogue) around one of the fields I would like to put a parenthesis. (nick_name). If there isn’t a nick name I would like the space left blank without the parentheses. I have tried if then but can’t seem to get it to work. Can anyone help? Thanks Dick I gather this is a mail merge main document. You need the field: { IF { MERGEFIELD nick_name } <> "" "({ MERGEFIELD nick_name })" } Insert the pairs of field delimiters (the things that look like braces but can't be typed from the keyboard) using Ctrl+F9. -- Suzann...

Questions about TaskPad behavior in OL2000 IMO
Hi, I have a couple of questions about the behavior of the TaskPad views in OL2000 (IMO), and I would love it if someone would be able to answer these for me. First, two of the available TaskPad views don't seem to be doing what they should. Can anyone tell me whether the following behavior is correct? * the "Today" view in the TaskPad shows: All tasks that (1) are due today _or later_ and (2) either are not completed or were completed today. (seems like it should show only uncompleted tasks *due today* ?) * the "Active Tasks for Selected Days" view shows: All ...

List Box format Question
Is it possible to code a list box if 7th Column shows "e" then that line turns yellow?? Thanks for any help...............Bob [lstModify].[column](6)="e" then color Yellow Not with versions of Access earlier than A2007. Listboxes have only a single set of properties but I think you can alternate row colors with A2007. I don't think you can highlight specific rows, you'd need to check. "Bob V" <rjvance@ihug.co.nz> wrote in message news:uvkUWF1bIHA.4040@TK2MSFTNGP05.phx.gbl... > Is it possible to code a list box if 7th Column shows "...

hlookup or conditional format?
Firstly I must scream AAARRRRGGGHHH!!! Ah, that's better. Right, I want the colour of a cell (well lots really). I want it to change when a different cells value = 2 SO imagine this if you will A1 white D1=0 A2 white D1=1 A3 white D1=1 A4 white D1=0 If the D cell values change, that's when I want the colours in the A cells to change:- A1 white D1=0 A2 Red D1=2 A3 white D1=1 A4 Red D1=2 I think I've explained it well enough. I think a conditional format would be the easiest way, but can I get it to work arrgghh! Many thanks for any help. -- EARTHWALKER ----------...

More than 1 cell reference/range in formula
Hi. To some formulas like countif(); sumif(), we can type 1 cell reference or cell range. How about if I wish to use to type more than 1 eg: countif(A1:A5, C1:C5, E1, E3, E5... However this method doesn't work. How to do? -- Additional information: - I'm using Office XP - I'm using Windows XP Hi, How about something like this: =SUMPRODUCT(COUNTIF(INDIRECT({"A2:A5","C1:C5","E1","E3","E5"}),">2")) Regards, KL "0-0 Wai Wai ^-^" <x@x.com> wrote in message news:unRw38xsFHA.1788@tk2msftngp13.p...

E-Mail Account Password Question
Hi, I've just installed Outlook 2003 on a notebook with Windows XP Home edition. I've setup Outlook for my e-mail account, but Outlook won't "remember" my password. Every time I check or send email I'm prompted for my account password. Any help appreciated. Thanks, Kyle Do you have the latest Service Pack and updates for Outlook 03? thanks "Kyle Stedman" wrote: > Hi, > > I've just installed Outlook 2003 on a notebook with Windows XP Home > edition. I've setup Outlook for my e-mail account, but Outlook won't > "re...

Calendar question #4
Is there a clean way to show that I'm not available except during office hours? I could give myself two daily appointments, and appointments in the weekends, but now my calendar is all cluttered up. The only thing I know of is setting the bounds of your work day but I don't think that marks the rest of the day as 'busy' "Howard Brazee" <howard@brazee.net> wrote in message news:ek4nLfzpFHA.620@TK2MSFTNGP15.phx.gbl... > Is there a clean way to show that I'm not available except during office > hours? > I could give myself two daily appointments,...

Check box questions (2)
Hi, sorry if this has already been answered somewhere before but trawled through and couldn't find anything. I am using check boxes to perform calculations based on TRUE or FALSE 1. How can I get the cell link in the Format Control to update if copy and paste several rows with several check boxes further down th sheet? Ie. one check box links to C3 but I want to copy that whole row dow one and have the check box autoupdate its link to C4. 2. How can I get a sum of multiple IF results from my check boxes? For example: =(IF(A1=TRUE,50,0))+(IF(B1=TRUE,50,0)) So that if both check boxes ...

license question
If I have windows 2k server with exchange 2k3, can I access it all day long from many linux clients using owa or evolution without needing CAL's? Talk to your microsoft reseller on that one. "Mostro" wrote: > If I have windows 2k server with exchange 2k3, can I access it all day long > from many linux clients using owa or evolution without needing CAL's? > > > ...

How Do I insert the keystrokes SHIFTF3 into a cell? #2
I am trying paste data from excel into another software program, In order to speed up data entry. In orde for it to work correctly I was wondering how I would tpye/insert the keystrokes ShiftF3 into a cell so that we I copy it over to the other software program it performs the function clear field(ShiftF3 performs the clear field command in the other software program). Or does anyone have any ideas how I would get around this issue? ...

label formating
I have a system with multiple labels. When a new front end is pushed out they all have to be reformated to the systems printer. I was thinking of creating a seperate access file to store just the labels (most updates don't require label changes). How can I print a report from a different database? That would be great. How do you format the label using VBA? "Dorian" wrote: > Why not keep the label formats in a table in the back-end to which each > front-end links. > -- Dorian > "Give someone a fish and they eat for a day; teach someone to fis...

VALUE appearing (referencing blank cell)
Hi Guys, sorry for again posting on here but this reoprt will be the death of me. I have 2 cells that have the following formlas IN C45 =IF(ISBLANK(AG149),"",AG149) IN D45 =IF(B45=1,'Base Data'!$H$52,IF(B45=2,'Base Data'!$I$52,IF(B45=3,'Base Data'!$J$52,IF(B45=4,'Base Data'!$K$52,IF(B45=5,'Base Data'!$L$52,IF(B45="pd",'Base Data'!$M$52,"")))))) Both C45 and D45 have no data in and are therefore blank - GREAT Then in cell AF45 I have the following formula which is...

Migration Question #5
Hello I have 2 SBS 2K3. One is hosting our web sites and exchange email. I would like to move all user accounts and email boxes as well as exchange config to the new server. How can I do this? Any help would be great. Thanks. -- Tom Cimino Cimino Brothers produce tom@broccoli.net 831-751-9150 ...

Excel 2007/format chart title/alignment/autofit
Why is 'Autofit' greyed out in Excel 2007/format chart title/alignment? (also greyed out are 'Internal margin' and 'Columns') ...

how do i add cells within a cell
I nedd to add 23 cells within a cell that is using a formula how do I add cells within a cell Question is unclear; please try to give us an example best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "dc" <dc@discussions.microsoft.com> wrote in message news:32080D07-5004-4508-9FA8-B28A64F325A3@microsoft.com... >I nedd to add 23 cells within a cell that is using a formula how do I add > cells within a cell ...

How to fill cell references from several worksheets in one?
Ok, so I have a workbook, each sheet is an order form, I want to reference the same cell on each sheet on a Master Tab that takes the info from each individual sheet and copies it to the master tab. I have referenced the cell on the first sheet (1) with an absolute cell reference (ie: ='1'!$C$1) and I have about 50 columns of this data. I would like to fill down and have the '1' from the formula change to 2, 3, 4, 5, etc. You would think there would be an easy way to do this. Anyone know how? I have been working on it for a while with no luck. At this point...

Question about build version.
I have several questions related to Visual Source Safe(VSS). I have a project saved in VSS. I want to freeze the project with its code while a milestone or release version is built. But I still want to continue development toward the next version. If the new version is not functioning, I still can go back. How to do it? Right now, I just burn a source code CD for each major release. I heard of using branching. But it seems I have to create a new project for each major release. Is that right? If yes, If later I want to roll back to privious version, how to do it? I am using Studio.NET to buil...

text gets cut in cells #2
when trying enter text in a cell (say the size of a letter for example) the text gets cut; only the cut version is printable, although the formula bar displays the whole text. How can I get all the text displayed in the cell (AND printed)? .. Hi, If I'm understanding you correctly, you need only widen the cell (column) and/or set the text to wrap if necessary. First, you can set your cursor over the column heading (A,B,C) just on the line separating the columns and drag to the right. OR you can position your cursor there and double-click, which will set the column to the best (...

Forcing text onto new line in cell
Hi, Sorry if this is obvious, but I can't see a way of forcing text onto a new line in a cell in Excel 2002, as one would get with say a carriage return in word. I know I can wrap text and this is set on, but for neatness I would like to show text entry more as a list within a cell (I can't use more than one cell in this instance). I can acheive the result by continous pressing of the space bar until the wrapping kicks in, but this seems an unlikely prefered method. regards Doug press alt+enter wherever you want to change line. Just like you press enter in word. "Doug Po...

Charts In 2002 Maintaining Formats
How do I maintain the formating of a dual axis chart in Excel 2002 when the data is updated? I have tried to set the chart type as the default and this work as long as I create a new chart. On the old chart when I update the data the chart immediately updates to a single axis stacked bar chart Help. Doug - Is it a pivot chart? These are notorious for losing their formatting after an update. If you used a particular chart type when constructing the chart, you should be able to reapply the same chart type. Right click on the chart (not just on a series), choose Chart Type from the pop up...