number returns only two decimal places after I change from text

Dear Colleague,
When the cell is formatted for text and I change to number, it only returns 
the number with two decimal places It will not change to a whole number or 
number with decimals.

I tried re-loading the application and that did not work? I have Excel 2000 
and sent the file to a 2002 user and it loaded fine.

I have seen in other applications from users that for some reason their 
numbers have quotations marks around them and they can't do formulas. Please 
help.

Thanks.

Louis
0
quale (3)
12/7/2005 8:49:02 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
284 Views

Similar Articles

[PageSpeed] 29

Louis

If the numbers are formatted as Text, simply re-formatting to number will not
do the trick.

Try this usual fix.

Format the cells as General then copy an empty cell.

Select the range of text numbers and Paste Special>Add>OK>Esc.


Gord Dibben Excel MVP

On Wed, 7 Dec 2005 12:49:02 -0800, "quale" <quale@discussions.microsoft.com>
wrote:

>Dear Colleague,
>When the cell is formatted for text and I change to number, it only returns 
>the number with two decimal places It will not change to a whole number or 
>number with decimals.
>
>I tried re-loading the application and that did not work? I have Excel 2000 
>and sent the file to a 2002 user and it loaded fine.
>
>I have seen in other applications from users that for some reason their 
>numbers have quotations marks around them and they can't do formulas. Please 
>help.
>
>Thanks.
>
>Louis
0
Gord
12/7/2005 9:37:37 PM
Changing the format to Number with 2 decimal places doesn't actually change the
value in the cell.

You could use another function to change the value, though.

if you have a formula that returns a value, you could wrap it in =round() (or
one of =round()'s several siblings).

=round(yourformula,0)

If you look at the help for round, you can click on "See Also" and see lots of
rounding functions.

=roundup()
=rounddown()
=ceiling()
=floor()
=trunc()
=int()
=mround()

Some of these are in the analysis toolpak, too.  So to use =mround(), you have
to have that addin loaded (but help describes how to do that).

quale wrote:
> 
> Dear Colleague,
> When the cell is formatted for text and I change to number, it only returns
> the number with two decimal places It will not change to a whole number or
> number with decimals.
> 
> I tried re-loading the application and that did not work? I have Excel 2000
> and sent the file to a 2002 user and it loaded fine.
> 
> I have seen in other applications from users that for some reason their
> numbers have quotations marks around them and they can't do formulas. Please
> help.
> 
> Thanks.
> 
> Louis

-- 

Dave Peterson
0
petersod (12004)
12/7/2005 9:38:17 PM
Dear Gord,
thanks for your reply but the whole spreadsheet has this problem, so I can't 
find an empty cell to set as General then copy. Even when I open up a new new 
workbook, it still opens like this. Do You have anymore suggestions. Thanks.

Louis

"Gord Dibben" wrote:

> Louis
> 
> If the numbers are formatted as Text, simply re-formatting to number will not
> do the trick.
> 
> Try this usual fix.
> 
> Format the cells as General then copy an empty cell.
> 
> Select the range of text numbers and Paste Special>Add>OK>Esc.
> 
> 
> Gord Dibben Excel MVP
> 
> On Wed, 7 Dec 2005 12:49:02 -0800, "quale" <quale@discussions.microsoft.com>
> wrote:
> 
> >Dear Colleague,
> >When the cell is formatted for text and I change to number, it only returns 
> >the number with two decimal places It will not change to a whole number or 
> >number with decimals.
> >
> >I tried re-loading the application and that did not work? I have Excel 2000 
> >and sent the file to a 2002 user and it loaded fine.
> >
> >I have seen in other applications from users that for some reason their 
> >numbers have quotations marks around them and they can't do formulas. Please 
> >help.
> >
> >Thanks.
> >
> >Louis
> 
0
quale (3)
12/8/2005 7:49:02 PM
Dear Dave,
It doesn't work. Thanks for your reply but the whole spreadsheet has this 
problem, for some reason using round() will not chage it. What do you put 
between the parentehesis?


Louis


"Dave Peterson" wrote:

> Changing the format to Number with 2 decimal places doesn't actually change the
> value in the cell.
> 
> You could use another function to change the value, though.
> 
> if you have a formula that returns a value, you could wrap it in =round() (or
> one of =round()'s several siblings).
> 
> =round(yourformula,0)
> 
> If you look at the help for round, you can click on "See Also" and see lots of
> rounding functions.
> 
> =roundup()
> =rounddown()
> =ceiling()
> =floor()
> =trunc()
> =int()
> =mround()
> 
> Some of these are in the analysis toolpak, too.  So to use =mround(), you have
> to have that addin loaded (but help describes how to do that).
> 
> quale wrote:
> > 
> > Dear Colleague,
> > When the cell is formatted for text and I change to number, it only returns
> > the number with two decimal places It will not change to a whole number or
> > number with decimals.
> > 
> > I tried re-loading the application and that did not work? I have Excel 2000
> > and sent the file to a 2002 user and it loaded fine.
> > 
> > I have seen in other applications from users that for some reason their
> > numbers have quotations marks around them and they can't do formulas. Please
> > help.
> > 
> > Thanks.
> > 
> > Louis
> 
> -- 
> 
> Dave Peterson
> 
0
quale (3)
12/8/2005 7:52:03 PM
If you have a formula that's in the cell, then you put that formula in the cell.

For instance, say you had numbers in A1:A10 and in A11, you had this formula:
=sum(a1:a10)

You could replace that existing formula with:

=round(sum(a1:a10),2)

To round that sum to 2 decimal places.



quale wrote:
> 
> Dear Dave,
> It doesn't work. Thanks for your reply but the whole spreadsheet has this
> problem, for some reason using round() will not chage it. What do you put
> between the parentehesis?
> 
> Louis
> 
> "Dave Peterson" wrote:
> 
> > Changing the format to Number with 2 decimal places doesn't actually change the
> > value in the cell.
> >
> > You could use another function to change the value, though.
> >
> > if you have a formula that returns a value, you could wrap it in =round() (or
> > one of =round()'s several siblings).
> >
> > =round(yourformula,0)
> >
> > If you look at the help for round, you can click on "See Also" and see lots of
> > rounding functions.
> >
> > =roundup()
> > =rounddown()
> > =ceiling()
> > =floor()
> > =trunc()
> > =int()
> > =mround()
> >
> > Some of these are in the analysis toolpak, too.  So to use =mround(), you have
> > to have that addin loaded (but help describes how to do that).
> >
> > quale wrote:
> > >
> > > Dear Colleague,
> > > When the cell is formatted for text and I change to number, it only returns
> > > the number with two decimal places It will not change to a whole number or
> > > number with decimals.
> > >
> > > I tried re-loading the application and that did not work? I have Excel 2000
> > > and sent the file to a 2002 user and it loaded fine.
> > >
> > > I have seen in other applications from users that for some reason their
> > > numbers have quotations marks around them and they can't do formulas. Please
> > > help.
> > >
> > > Thanks.
> > >
> > > Louis
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
petersod (12004)
12/8/2005 8:22:42 PM
Reply:

Similar Artilces:

How do I get excel to accept (c) as text and not change to copyri.
How do I enter the text (c) in Excel without having it changed into the copyright symbol? Hi Daffyd, Try: Tools | Autocorrect | Select (c) | Delete | OK --- Regards, Norman "daffyd" <daffyd@discussions.microsoft.com> wrote in message news:8CCC3C1A-6F19-4F62-B934-8A71F236A4FD@microsoft.com... > How do I enter the text (c) in Excel without having it changed into the > copyright symbol? Go into the Tools Menu. Look for AutoCorrect. In the bottom half of the AutoCorrect Tab, look at the list for Replace text as you type. Delete the entry for (c). tj "da...

Sequentially numbered Purchase orders
In the past with Office 97. I used a purchase order template that would basically self number itself. when it opened a small box would appear and by clicking it the PO received a unique number. How can I accomplish this using Office XP. ...

Stagger X-axis text
In 1-2-3 I could stagger the text in the X-axis. In Excel it seems that I can only rotate the text to 90 degrees. Is there a way to stagger and leave horizontal? Specifically, I have all the provinces (or 10 of them) across the axis and they take up room when spelt out (no abbreviations allowed). I would rather the first, third, fifth ... etc. be higher and the second, fourth etc. be lower to allow the chart to be narrower and still read the text clearly. Cheers, Deborah >-----Original Message----- >In 1-2-3 I could stagger the text in the X-axis. In Excel it seems Deborah I would...

exporting a report in text
I have data in one table for which I have created formatted reports - 1 by program number and 1 by last name. I have the reports the way I want them to look. I need to have them in text-delimited format. The reason for this is that I need to be able to upload them into a DREAMWEAVER table. If they are in the text-delimited format I can upload all the reports fairly easily. Otherwise, I have to export them to WORD as a table, and then laboriously upload them to Dreamweaver column by column as DW will not accept the whole report. (It is doable, but not too convenient. Than...

how do you place a spreadsheet(s) inside another spreadsheet
I am trying to setup a workbook with spreadsheets that contain other spreadsheets. Does excel allow this? if yes, how can I do it. Hi not 100% sure what you're after here ... a workbook can contain multiple worksheets. Each worksheet contains a grid of cells 256 columns wide & 65536 rows deep. This basic structure of excel can not be altered. Please post back with additional details on what you're trying to achieve and we might be able to suggest a solution. Cheers JulieD "ucastores" <ucastores@discussions.microsoft.com> wrote in message news:DDE44C32-...

Numbers in a text field-can I add them up?
Hi everyone! Using A02 on XP. I have a table of data with survey response fields that contain a 0,1,2,3,4 or 5. However, the fields are formatted as text, not numbers. I need to add up certain blocks (Items 1-6, Items 7-23, etc.) and then do some averaging. I cannot change the field types from text. Must I append to a new table or can I do something right in my query? I've got one field in my query like this: ES: [Item1]+[Item2]+[Item3]+[Item4]+[Item5]+[Item6] My result is: 553453 or 554444, etc. I want: 25 or 22, etc. I would really appreciate any help or advice. Thanks...

how to query my web site from VBA and return a value to VBA
Hello All, From VBA I would like send a value to my web site, and have it return a value. I've learned how to use FollowHyperlink to send a value to an ASP script, but how can the ASP script send a value back to VBA?? Thanks, Brian Austin, TX You can use xmlhttp to make a request to your web page: '********************************************************* Sub Tester() MsgBox WebResponse("http://www.mydomain.com/myactualpage.asp? info=3Dblah") End Sub Private Function WebResponse(sURL As String) As String Dim XmlHttpRequest As Object Se...

Publisher 2000 changes default font
Having loaded Publisher 2000 onto a Win98 m/c I have discovered fonts used by Groupwise have changed. This implies that P2000 has changed the default font somewhere - any ideas please? How do I change the fonts in GroupWise http://support.novell.com/cgi-bin/search/searchtid.cgi?/10061270.htm -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.mvps.org/msauer/ news://msnews.microsoft.com "PS" <anonymous@discussions.microsoft.com> wrote in message news:1be4401c45220$f2b1b4a0$a601280a@phx.gbl... > Having loaded Publisher 2000 onto a Win98 m/c I have > discover...

Change outbound server in header to fix 550 Can't verify your host name error
The headers on the outbound emails show the internal DNS name of our exchange server; obviously this won't resolve properly at the destination. How/where in Exchange 5.5 can I force the IMC to use a real fqdn on outbound mail? Thanks! Frinky You can do this in TCP/IP properties\Advanced\DNS tab of machine. And yes, this is not just for Exchange, so you may consider forwarding all outgoing mail to some relay server (your firewall or ISP's server). Professor Frink wrote: > The headers on the outbound emails show the internal DNS name of our > exchange server; obviously this...

Outlook and Exchange with two domains
Hi, I have an Exchange 2007 server that hosted a single domain, abc.com. I added a second domain, xyz.com by creating an accepted mail domain so that Exchange receive inbound mails to xyz.com, created email address policy so that recipients would get the mail addresses, and created MX record to point to Exchange server. While I am waiting for the MX record change to propoagate, I want to setup Outlook but I am not sure how to set it up. I cannot create a second email account, since only one Exchange server account can be created. I am not sure if I need a second account since users in AD show...

How do I set the number format to Base 12?
I would like to change the number format on my spreadsheet from Base 10 to Base 12, eg. 12 bottles makes up 1 case. Therefore, if I were adding up three different cells 9 bottles + 11 bottles + 6 bottles, my result should be 2 cases 2 bottles if possible 2.2 in a case column. See http://www.cpearson.com/excel/fractional.htm for details. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Andrew Moore" <AndrewMoore@discussions.microsoft.com> wrote in message news:893CABE9-37D7-4E6B-8A7E-A5E679C8C824@microsoft.com... >...

Add Record two table
Hi Guys, I have a common field (ProjectNumber) in two table.Between table there is one to many relationship. I have a form to add new ProjectNumber with other information. What i want is that, when i add new projectNumber it should add to both the table. Thanks & Regards Hi Nad, The correct process is for a ProjectNumber to be added to only the Project table when a new project is added. A common way to set this up is to have the main form bound to the table on the one side of the relationship and the subform bound to the table on the many side of the relationship. To add new proj...

Synchronize two seperate Exchange accounts (only calendar)
Hey First my objective. I have a personal exchange server / oulook 2003 solutiono for personal use with my private email address. Then i have another Exchange / outlook 2003 account at work. OK. I want to two mailboxes separate but i would be very nice to be able to share my calendar (and if possible contacts) between the two accounts. Much like a PDA=>PC sync solution. It does not have to be automatic. Eg. a 3 party product or so where I could press "Synchronize" would be just fine. But first of all. Is it possible? Thanks in regards Anders No. Outlook doesn't su...

why do changes only save locally on networked excel file?
why do changes only save locally on networked excel file? ...

How can I stop charts from refreshing when changing source data?
My problem is, that I am working with a lot of data and when I change some of the ranges all charts in my view refreshes and it takes much time. My pc is aP4 3GHz, 2GB RAM so that should not be the bottleneck. Is there any way to force the charts not to update all the time? ...

VISIO 2007 -Text direction
can some one tell me how to change text to be type in vertically. Under tools, options there is no regional tab or under format text the change text direction command does not work. "kgbrat" <kgbrat@discussions.microsoft.com> wrote in message news:2DBF18B5-E1C8-4493-8BEF-F7D4C1538781@microsoft.com... > can some one tell me how to change text to be type in vertically. Under > tools, options there is no regional tab or under format text the change > text > direction command does not work. You can use the Text Tool (The A with an circular arrow around it) and gr...

Count on cell for each time it is changed
I would like to count the number of times a cell has been amended/ changed e.g cell b2 has the word red it it, in cell c2 i would like a counter for everytime the cell in b2 has changed, at the end of the day you get final number. Is this at all possible? Jelinek, You can do it by putting the following VBA macro in your sheet: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then Cells(2, 3) = Cells(2, 3) + 1 End If End Sub Art "Jelinek" wrote: > I would like to count the number of times a cell has bee...

Help ! formatting data to text
I am creating data in an Excel spreadsheet. I then want to get that data into a simple text email. I have some problems and questions... 1) how do I get the columns of data to line up evenly when I copy the data to email text ? Keep in mind I need to be in simple text format, not HTML or rich text. Every time I do this, all columns become chaos and are unreadable. 2) Is there a simple way to automate the creation of an email from an excel file ? this is less important to me. Thanks in advance WxMachine #1. I think it may have to do with what email client you use, too. I copy and ...

Conditional sum
Hi Can you help? I would like to sum a matrix according to a condition in a row and in different condition in a column. I know how to do this one dimensionally (either in the row or the column) using sumproduct, but what about two dimensions? Let me give an example, number of widgets produced each week by each type of machine A B C D 1 weeks>>1 2 3 2 Type 1 10 15 11 3 Type 2 20 5 10 4 Type 1 5 12 21 Etc So, I’d like the formula to be a...

splitting one column into two columns ... not what you think
i have fixed column widths that can't be changed; nor can any other columns be added to the worksheet ... i've got data in one column that represents "results" ... within the results column though, i need two columns (starting directly below the "results" cell, one that reads "in range" and that other that reads "out of range" ... so: if i'm on [column a] [cell 1] i want: "results" ... in [column a] [cell 2] i want: "in range" AND "out of range" with a line down the middle. "text to column" is ...

Keeping Sent Items in sync across two PCs
I asked this question a few days ago in the microsoft.public.outlook.general forum but didn't get any replies, so I thought I'd try here instead... I have two computers, and both use Outlook 2000 for email. I don not use an Exchange server - email is by POP3/SMTP. Both computers collect the same email by POP3, and are set up to leave messages on the server for 30 days so nothing is missed on either PC. This works well, but the only problem is that when I send an email, it is only in the Sent Items fold of the PC that I used to send the email. Is there a way to 'copy' group...

hide/change color of selected row headings
Is it possible to hide the row heading numbers for selected rows (i.e. rows 51 and greater) for just the selected sheet? I'd like to have a color with no row heading number appear that matches the fill color I select for the adjacent cells. Secondly, any ideas as to how I can prevent the user from scolling further down than a certain point (i.e. row 51). Thanks in advance. Joe Row headings are either on or off, you can't hide some. However, you can achieve a similar effect by hiding *all* headings (Tools/Options/General) and putting the numbers 1:51 in A1:A51. to limit scro...

Adding up negative numbers only
Ok, let me try to explain this problem. I have several cells a1 throug a10. Each cell has a number, for ex. $10.00, ($5.00),etc. , sometime this number is positive, sometimes it is negative. I want to only ad all of the negative numbers in cells a1:a10 and put a total in cel a11. How can I do this? Thanks Ton -- tonydep ----------------------------------------------------------------------- tonydepo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1530 View this thread: http://www.excelforum.com/showthread.php?threadid=26928 =SUMIF(A1:A10,"<0&quo...

Two different hyperlink colors
I have created a publisher document that I am using as email stationary. I have created some blue buttons that I have put hyperlinks on. I want the hyperlinks to not show up as blue on the buttons, so I changed the color scheme so they show as white. However, in the main body of my email, there are also a few hyperlinks. Because I've changed the color scheme, these hyperlinks are also showing up as white. I've tried just highlighting them and changing the font to black but that does not work. Is there anyway to change the hyperlink color in the body of my email? Than...

changing a name in mulit parts of a document
I am setting up a word document. This will be used as a template In this document I wish to insert the same "name" throughtout this document. Is it possible to change the name in one spot and all of the others change automatically thanks See http://gregmaxey.mvps.org/Repeating_Data.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>...