Cell Formating Problems

This is what I do:
I open up a brand new worksheet.
I format cell A1 to text.
Then I enter the number 1 into it.
Then I format the cell to Numeric
and the 1 in the cell does not change.  it stays as (left justified

If I delete the 1 and type a 1 into that cell and hit Enter, it become
1.00 (a number).  

Whats happening here???  Why doesn't it convert the 1 to a 1.00 when 
format the cell to numeric?  Why do I have to re-enter the number

Message posted from http://www.ExcelForum.com

3/1/2004 11:14:09 PM
excel 39879 articles. 2 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 53


It was originally entered as Text.  Simply re-formatting doesn't change its
underlying value.  Formatting is used to change the "look" of data, not the
original value.

Either enter as a number originally or if entered as text, format to "number"
then hit F2<Enter>, which effectively re-enters it.

If you have a great whack of these to change, copy an empty cell formatted as
number then select your range of data and Paste Special>Add>OK>Esc.

Gord Dibben Excel MVP

On Mon, 1 Mar 2004 17:14:09 -0600, brain21
<<brain21.12gplj@excelforum-nospam.com>> wrote:

>This is what I do:
>I open up a brand new worksheet.
>I format cell A1 to text.
>Then I enter the number 1 into it.
>Then I format the cell to Numeric
>and the 1 in the cell does not change.  it stays as (left justified)
>If I delete the 1 and type a 1 into that cell and hit Enter, it becomes
>1.00 (a number).  
>Whats happening here???  Why doesn't it convert the 1 to a 1.00 when I
>format the cell to numeric?  Why do I have to re-enter the number?
>Message posted from http://www.ExcelForum.com/

3/1/2004 11:51:17 PM

Message posted from http://www.ExcelForum.com/

3/2/2004 1:45:05 PM

Similar Artilces:

Reference cell contents
I am working on a spreadsheet that contains various comments from various sources. I would like take these comments and divide them into different workbooks by each source. For example Source 1 = comments in cell C5, C8, & C10 Is there a way to do that in Excel? ...

How to keep loaded file's format intact?
Hello, I am loading into spreadsheet .csv file, where each field is enclosed in quotation marks, and separated by the comma. Each line looks like this: "No name"," 1 555 2340553","2009-09-07 14:55:08","","No Dialtone. Line is not connected or it is in use.","8" That file loads nicely into spreadsheet, each field into its own cell. However, when saving it, the format (quotation marks) is gone. I need to preserve that quotations marks. The reason for it is that some data may contain commas which are not field s...

Conditional Formatting
I have a row of cells that calculate the totals for their columns but I force to be blank via IF statements until certain conditions are met. Once these conditions are met, the total appears. I'd like to also highlight these total cells via conditional formatting. My conditional formatting input is simply "If Cell is Greater than 0, format". For some reason all the cells format as though they are all > 0 yet they don't display anything. Are there some internal rules that specifiy when a cell is really 0? Is there another way to do this that won't care what the for...

Worksheet Problem 01-04-10
So I have a couple of issues. I go to open up a file today and try to unhide two worksheets that were hidden, and they are gone. I can still see them in VBA, but cannot open them and they are not visible at all in excel. I also am having trouble inserting a new worksheet. All the options are they, but I am not able to select insert. Any help would be appreciated. CHeck to see if workbook protection is activated. That would prevent you from being able to add new sheets (or unhide). Also, in VBE, check to see if the missing sheets have a visible status of "xlSheetVe...

Outlook Connector Beta works with some accounts but others are problems
I just installed the new Microsoft Outlook Connector beta (I'm using it with Outlook 2007 and BCM)and have had a little success and a little failure. It works with two of my Microsoft Office Live email accounts (the free ones) and not with the third (which is also a free one). I also have a MSN account (tdjgordon@msn.com)that I've had for many years and I thought that it definitely work with this one, and it does not. Anyone have a clue how to get the third Office Live account working and most also my trusty MSN account? I should point out that I have also been using the Window...

Excel document changes format
Hi, I have an Excel document that changes format unexpectedly. It seems to change the format of the document eventhough i saved it. I have to resize the windows of the bar graphs, etc. It just doesn't save it the way i want. I re-installed MSOffice and re-did the NT profile, but still persists. Any ideas? Thanks. Excel does seem to have problems if the screen zoom is anything othe than 100%. Usually changing to 100 and back resets he screen display -- Message posted from http://www.ExcelForum.com ...

Export Access Record to Specific Cells in Excel
I'm looking for suggestions on how to export data from an Access table or Query into specific cells of a Worksheet. The table I'm importing will always contain a single record only. For example, I'd like to export data from Field1, field2...etc in an access table to Sheet2!A3, Sheet2!B3...etc. Any sugestions would be most appreciative. ...

Problems installingOutlook 2002 on new computer
I just got a new computer at work and I'm trying to install Outlook 2002 so I can "Active Sync" my Ipaq 3800 w/ this new computer. Outlook 2002 seemed to installed just fine but when I try to open it, it tells me I need to create a user profile. I have done this about 10 times now and the system doesn't recognize any profiles. It also give a 2nd error message that says that "the server is not available". I am not trying to hook up to a server so I'm wondering if there is a setting somewhere that is incorrect and Outlook is looking for a server. Can a...

Can't display all text in cell
I'm trying to display text in a cell. I've got Wrap Text on, and I hav several paragraphs of text. All of it appears in the formula bar, bu not in the cell displayed on the screen. And yes, I increased the ro height, but it still doesn't show. Any ideas? Thank -- Message posted from http://www.ExcelForum.com I am having the same problem. Apparantly there is a 1024 character limitation per cel in excel. The only advice I have received so far, which does work, is to press alt+enter to create a line break. If you find another work around, I would be interested. Hi besid...

MSCRM Reports Export format
Does anyone know if there is a way to export reports as Rich Text Format (*.rtf)? or exporting to a CSV? We have a customer that still uses Wordperfect, and exporting to an Excel or Word Doc may not be an option. ...

Formatting Cells #13
If you want something from cell (A) to appear in cell (B) and change when the contents of cell A changes, how do I do that? Acurran, =A1 ??? -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 2003 ** remove news from my email address to reply by email ** "Acurran" <Acurran@discussions.microsoft.com> wrote in message news:E800CB99-E6BA-4DF3-BCA0-445A89B2E8A8@microsoft.com... > If you want something from cell (A) to appear i...

[Bitmap] problem loading from array
Hello! I'm trying to load a bitmap from an array using the following code : CBitmap bmp1; bmp1.CreateBitmap(im_data.width,im_data.height,1,24,NULL); bmp1.SetBitmapBits(im_data.length,im_data.data_buf); CClientDC dc(this);//CPaintDC dc(this); CDC bmDC; bmDC.CreateCompatibleDC(&dc); CBitmap *pOldbmp = bmDC.SelectObject(&bmp1); dc.BitBlt(50,150,im_data.width,im_data.height,&bmDC,0,0,SRCCOPY); bmDC.SelectObject(pOldbmp); where im_data.width is the width of the image where im_data.height is the height of the image where im_data.length is the number of bytes in the image ...

Problem using a report from Access2003 to Access2007
I am trying to use a database created with Access 2003 in Access 2007 but have come up with a problem. The database contains a report which produces invoices for sales between two dates. When the report is run the user is asked to input sale date From and To (this is set up in the Invoice Query), Invoice Date and Invoice Start Number - these two are set up as text boxes on the report. Invoice Date conrol source has [Enter Invoice Date] and Invoice Start Number has [Page]+[Invoice Start Number]-1. This works fine in Access 2003 and the invoices come out correctly but in 2007 invoice ...

Need help with cursor spacing problem
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3173510125_666133 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: 8bit Hello: I�m trying to fill in a form that�s MS Word based. I�m using MS Office X on a Mac G4 with system 10.3.4. When I type, the cursor seems to have an extra space between the last letter and the place where I want to put a new one. In other words, I�m unable to align the cursor exactly where I want it to go. It makes typing almost imposs...

Modify Protection Macro to allow formatting?
Option Explicit Sub UnprotectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.unprotect password:="topsecret" next wks End Sub Sub ProtectAll() dim wks as worksheet for each wks in activeworkbook.worksheets wks.protect password:="topsecret" next wks End Sub How do I modify to allow users to format cells? Only been 4 minutes since your first post. Have a little patience. See reply at that post. Gord Dibben MS Excel MVP On Thu, 11 Feb 2010 11:14:02 -0800, Visakha <Visakha@discussions.microsoft...

The format of the e-mail address is incorrect
Good Morning, I'm getting the following message when attempting to send mail to anyone with an apostrophe in the domain name (ie. john.smith@wendy's.com) The following recipient(s) could not be reached: 'mark_eganhouse@wendy's.com' on 2/22/2005 2:13 PM The format of the e-mail address is incorrect. Check the address, look up the recipient in the Address Book, or contact the recipient directly to find out the correct address. Any help would greatly be apreciated get rid of the ' it is wendys.com "JZaragozaJr" <JZaragozaJr@discussi...

counting same text from different cells
Hi I was Trying to count text such as "p" for pass from different cell in excel but i am not sure what is the formula for that. It will be really helpful if u could send me the solution thank you om Hi =COUNTIF(A1:A100,"p") -- Regards Frank Kabel Frankfurt, Germany "om" <om@discussions.microsoft.com> schrieb im Newsbeitrag news:96B25715-6B88-40F8-8360-9602A05E3B30@microsoft.com... > Hi I was Trying to count text such as "p" for pass from different cell in > excel but i am not sure what is the formula for that. > > It will be rea...

pulling cell "value"
in the cell C4 i have the formula =now() and I have that cell formatted to custom mmmm so that it displays as January. I am now trying to write some VBA code to look at cell C4 and take the January and then do a vlookup on the array that i have named months. months is the cells D14:E25 column D has a list of the months and column E has a list of numbers that corresponds to these months. This is a custom list so I can not use the standard numbers that excel uses for the months. The vlookup in vba should take the January and find the number in col E that corresponds to it th...

System restore problem
Windows XP Home SP3. I'm trying to do a system restore, by going to Start | Accessories | System Tools | System restore. I select a date, and click next, but nothing happens. I've tried several different dates with no luck. Am I screwed or is there a way to manually force a system restore without using this wizard? Thanks in advance for any help. Craig Try SR in Safe Mode. Why do you want to run SR? Are you having a problem? "Craig Schiller" <cschiller1@earthlink.net> wrote in message news:u9722H$sKHA.5276@TK2MSFTNGP02.phx.gbl... > Win...

y-axis problems
Hello, When I create a bar chart (in powerpoint or excel) on my PC, the last letter on the X axis label gets cut off when I rotate the label to run sideways. It works fine on my laptop, but if I open the file on my PC and click on the chart area, the last letter disappears. This is a very common experience. Some uses add "hard-spaces" which is ANSI 0160 - hold ALT down and type 0160 on numeric pad Other add some nonsense text (three periods) and format it with a color and background to make invisible. Best wishes - Bernard Liengme www.stfx.ca/people/bliengme remove CAPS ...

I'm trying to use this class but documentation about it is minimal. I'd like to know: 1. How shoud I create this control to have edit mode without any margins like after this: <body leftmargin="0" topmargin="0" rightmargin="0" style="color: black; background-color: yellow;" alink="red" link="blue" vlink="navy" > 2. How intercept pressing Enter to make additional operations. 3. How put any text to this control from program (no from file, address. etc.) 4. How set caret to the end of line (for instance a...

Switchboard Format
How do you open switchboard pages (not the main page) in the design screen so you can edit the buttons, etc. Thanks in advance. -- S Afaik, you don't use design mode for the switchboard, other than making cosmetic changes. I believe all switchboard pages use the same form as a template, so each page will have the same "look-and-feel" as the others. The items placed on those pages are generated at runtime based on the table entries ("SwitchboardItems") that were created via the Switchboard Manager. Tools>DatabaseUtilities>SwitchboardManager. HTH, &qu...

Merge Cells, but keep backgrounds
In excell 2003 is there a way to merge cells so the text spans them all, but keep the current background of the cells (which are not all the same color)? No Try Center Across Selection and avoid merged cells whenever possible. Gord Dibben MS Excel MVP On Sat, 30 May 2009 09:16:12 -0700 (PDT), "dan@danirwin.net" <dan@danirwin.net> wrote: >In excell 2003 is there a way to merge cells so the text spans them >all, but keep the current background of the cells (which are not all >the same color)? Is there a way to center across selection vertically or just horizonta...

How set totals of various cells with per colour fill value?
I have a sheet containing values as follows. April May June company 1 43 company 2 5 company 3 17 The cell values are also coured to indice a status. Somewhere beside the table I want to mention the totals of the cells, per status (so per colour). How to perform the latter? Thank you. Bart Excell 2003 See http://www.xldynamic.com/source/xld.ColourCounter.html for a working solution -- HTH Bob Phillips (remove xxx from email address if mailing direct) "AA Arens" <bartvandongen@gmail.com> wrote in mes...

Catalog & Excel formatting
I am using Excel as a data source for a catalog. One of the price fields in Excel is a 'currency' format and is the result of a formula. Publisher imports the field but only displays the figure as 45 instead of £45.00. Anyone know of a solution to this problem ? I can get around it by re-formatting the field as a 'number' format but as most catalogs will involve lists of products with prices I think the 'currency' format should work. Thanks. The best thing to do is do a SAVE AS in CSV (Comma Separated Values) and add a � to the figures and then copy and paste th...