How do I stop last number from changing to a 0 in excel

I have a worksheet that has one column that has account numbers. If I enter 
16 or more digits the last numbers change to a 0. I have set, cell format to 
numbers with no decimals.
0
dan12 (1)
1/10/2006 8:29:02 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
555 Views

Similar Articles

[PageSpeed] 39

Try setting cell format to text before entering or use an apostrophe then 
enter the number

HTH
Regards,
Howard

"dan12" <dan12@discussions.microsoft.com> wrote in message 
news:426335E4-F08B-4C23-B6E2-EB45F36101F5@microsoft.com...
>I have a worksheet that has one column that has account numbers. If I enter
> 16 or more digits the last numbers change to a 0. I have set, cell format 
> to
> numbers with no decimals. 


0
lhkittle (223)
1/10/2006 8:35:47 PM
Excel cannot handle "numbers" that large...Try this

Select the cells that will contain account numbers
Format>Cells>Number tab
Category: Text
Click the [OK] button

That will change the format to treat every value as text. 

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"dan12" wrote:

> I have a worksheet that has one column that has account numbers. If I enter 
> 16 or more digits the last numbers change to a 0. I have set, cell format to 
> numbers with no decimals.
0
1/10/2006 8:39:03 PM
Excel will only display up to 15 significant digits.  So anything beyond
the 15th number will display as a zero.  You can either set it up to
display in scientific notation or if you will not be using the numbers
for any math then simply put an apostrophe (') in front of the number
to display it as text.


-- 
mphell0
------------------------------------------------------------------------
mphell0's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30153
View this thread: http://www.excelforum.com/showthread.php?threadid=499889

0
1/10/2006 8:41:02 PM
Reply:

Similar Artilces:

function for last non-blank entry in column?
Is there a function that would return the value in the last non-blank cell in a colunm? example "a1" = 10, "a2" = 25, "a3" = 3, formula in "a10" would produce 3 then if later "a4" = 1, "a10" would automatically = 1 (in other words it's not Max, nor Min, nor Sum, nor Average etc) is that clear? thanks mark On Dec 26, 12:12=A0pm, "mp" <nos...@Thanks.com> wrote: > Is there a function that would return the value in the last non-blank cel= l > in a colunm? example > "a1" =3D 10, > "...

How do you remove the outside border on a chart in Excel 2007?
I have created several charts and an outside border appears on the page. I want to remove that border. ...

Regarding Reading XML Files in Visual C++6.0 using XMLDOMMethod.
Hi, While reading a 4.2 MB XML File in Visual C++ 6.0 using XMLDOM method, it is taking lot of time. But the same XML File when reading in Visual C# using Visual Studio 2005 is taking lesser time. How this Visual C++ 6.0 reading delay in XML can be avoided. ==========Suresh ...

disable snap-to while dragging tab stops?
How do I drag a tab stop to arbitrary position without it snapping to the tick marks on the ruler? I tried holding down shift, ctrl, alt. None of them works. I even turned off everything in the arrange->snap menu. Publisher 2003. Set the tab to about where you want it, zoom to 200%, open the tab dialog and input the number where you want the tab positioned, click set. The tab button can be dragged to your toolbar by customizing. This is the best that Publisher can do... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "p...

sum formula not working excel 03
The formula: =sum(h10,h14,h20,h40) The result should be: =sum(4,5,4,4,4,) =21 I however get 20 as a result. Tools/Options is set to automatic and the "h" cells are formatted as numbers (1234). The cells are dependent on other formulas (=sum(h11:h12)/2) etc. The cells in between is formatted to numbers (1234.10) Any suggestions anyone Check out whether h10,h14,h20 and h40 contain real integers..or are rounded off. Increase the decimal points of these cells and check .. -- Jacob "wynand" wrote: > The formula: > =sum(h10,h14,h20,h40) >...

auto insert of last day of last month
Hi, I'd like to create a button such that on clicking it, cell A1 will show the last day of last month, for example, today is 20/5/04 so on clicking the button, it'll show 30/4/04 in cell A1. I tried using the following macro which I had used previously for other purposes but it doesn't seem to work in this case: With Range("A1") .Value = DateSerial(Year(.Value), Month(.Value) + 2, 0) End With What code shld I use? Thanks! val Hi Dolphinv4! You could use the cell formula: =DATE(YEAR(TODAY()),MONTH(TODAY()),0) Or use the subroutine: Sub LastDay...

Numbers instead of ABC?
Hi all How come I see numbers 1...x instead of the standard A B C columns? I have Office XP... Please advise Guy I don't know how it comes but you can change it back with Tools>Options>General, uncheck R1C1 reference style -- Kind regards, Niek Otten "Guy Cohen" <noemail@please.com> wrote in message news:eVM50$wVGHA.900@TK2MSFTNGP15.phx.gbl... > Hi all > How come I see numbers 1...x > instead of the standard A B C columns? > I have Office XP... > Please advise > Guy > Click on <Tools><Options><General> and goto <Se...

add number to existing text numbers
I have 2000 columns with accounting type text numbers in each cell, i.e. 0345999, 058956, ect. I want to add an "0" in front of each account number. Anyone know how to do this withount going into each cell and doing it by hand? Hi one way: use a helper column with the formula ="0" & A1 copy this down for all rows, copy this column and insert it with 'Edit - Paste Special - Values' -- Regards Frank Kabel Frankfurt, Germany Stacy wrote: > I have 2000 columns with accounting type text numbers in > each cell, i.e. 0345999, 058956, ect. > > I wan...

stops responding
A few days ago while trying to balance one of my accounts, Money would start hanging and I had to kill the process and created a LRD file. My file is only 12MB and it has been fine until recently. I am using Money 2007. I should have put my system specs. I have a P4 3.8GHZ w/ 2GB RAM running XP Pro. When I have Money open, I typically only have Messenger, a browser, and sometimes Acroabat Reader running also. "Casey" wrote: > A few days ago while trying to balance one of my accounts, Money would start > hanging and I had to kill the process and created a LRD file. ...

suppress decimal point combine print word and excel
When I use word to combine print (office 2002). some excel numberic value is long than I expect, I want to suppress decimal point only two digital . for example 20.22233222424 trans to 20.22 how can I do ? scwu@dfjh.tpc.edu.tw Hi in Word you can apply a format to imported fields or round your data in the Excel file with ROUND -- Regards Frank Kabel Frankfurt, Germany thomas wrote: > When I use word to combine print (office 2002). > some excel numberic value is long than I expect, I want to suppress > decimal point only two digital . > for example 20.22233222424 trans to 20....

Duplicate numbers
I am doing a copy and paste with phone numbers on my spreadsheet. I then manually have to search for duplicates. I there a way that excel can do that for me? Also, When I copy phone numbers, I must copy one number, go to spreadsheet and paste. Back & forth. Is there a way to highlight and copy all of the numbers I need on the page in one shot and then paste to my spreadsheet? Can't scroll down because they are far apart from eachother. Hope someone can help. kbkst -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200602/1 try using the autof...

paste from micrografx 10.0 problems
After upgrading to office 2003 can't paste from micrografx into publisher. did office 2003 chage how the clipboard works or something? Can you paste special? Have you looked at Corel's support documents? I have no problem pasting Corel images if I use "paste special." It maybe the same for Micrografx. I've looked around for support for Micrografx, it is not easy to find. Might be simpler to save the image as something other than .drw and inserting into Publisher. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.co...

Convert Number Field to Date using query
I have search the forum and found several people asking this question, but my situation is a little different. I have a database that I need to convert a number field to a date, but the number fields have the date as "08/07/2007". I have not been able to find a way to convert this number field to a date. Any suggestion/comments would be greatly appreciated! Thanks You sure it's a numeric field? Numeric fields wouldn't have slashes in them. If it's text, you can use the CDate function. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please...

Excel opens with an error report whenever I Boot Up my computer
A couple of days ago, I noticed that whenever I boot up my computer, an error report comes up indicating that Excel needs to close because of error. I unistalled office and the message wnet away. But when I reinstalled Office, the message came right back. Thoughts? ...

blue stop screen 0x0000007E
stop error code after windows installs drivers. this is new hdd and mobo but problem persists.oxoooooo7e (oxf748eobf oxf78da2o8,oxf78d9fo8....was doing it with last mobo and hdd...help please!!! -- thanks one and all for your helping hands xerxies wrote: > stop error code after windows installs drivers. this is new hdd and > mobo but problem persists.oxoooooo7e (oxf748eobf > oxf78da2o8,oxf78d9fo8....was doing it with last mobo and hdd...help > please!!! That *is* a driver issue. You are installing the incorrect drivers or need to contact the manufacturer of said ...

QuickBooks export to Excel
Suddenly, my QuickBooks program no longer allows me to export to Excel. Worked fine a week ago. I can log onto QB from every other pc in the office, and export to Excel works. Any ideas? Since you are exporting from QB I would start at that end. What trouble-shooting have you done? What message do you get when you try an export from QB? Gord Dibben MS Excel MVP On Fri, 30 Apr 2010 06:41:02 -0700, SecurityGuy <SecurityGuy@discussions.microsoft.com> wrote: >Suddenly, my QuickBooks program no longer allows me to export to Excel. >Worked fine a week ago. I...

Portal 2.0 installation
The resource cannot be found. Win srv 2003 Sharepoint services standard 2003 Biz Portal 2.0 Can't get anything out of this. The web/application server installed, the portal installed with a domain admin account, the sql user account is the same account that ran BP 1.2. The default web site has been extended. Have tried .Net 1.0 and .Net 1.1. Can't seem to log into anything. http://srvname/businessportal gets 403 forbidden, and http://srvname/businessportal/default.aspx gets The resource cannot be found. Version Information: Microsoft .NET Framework Version:1.1.4322.2032; AS...

Transposing list of numbers
I'm using Excel 2003. I have a list of 400 numbers in Column A, I would like to transpose it across 7 columns and 58 rows. Is there an easy way to do this? I understand I can do the copy/paste special/transpose for 7 at a time. Thank you. -- jlhcat Sub ColtoRows() Dim rng As Range Dim i As Long Dim J As Long Set rng = Cells(Rows.Count, 1).End(xlUp) J = 1 On Error Resume Next nocols = 7 'InputBox("Enter Number of Columns Desired") For i = 1 To rng.Row Step nocols Cells(J, "A").Resize(1, nocols).Value = _ ...

recipient policy stopped working
Hello All, I'm seeing an issue in my Exchange 2003 environment where an admin will create a new account and the recipient policy does not populate the users email address. This just started happening a few days ago here in the US... It happened in our UK office last week but I thought that might have been user error on the admin. We are in mixed mode with Exchange5.5, which we have been for almost a year. I did how ever remove some old 5.5 servers but the last one I removed was almost a month ago, so i don't think it would be that. I know you need to ask this question so yes th...

Splitting numbers from cells and sum them
Hi! I need a FUNCTION that it will do the following: I have cells with values from 1 to 45. I want to split the numbers i the cells and then to sum them. The results will be of course to othe cells. So if i have the number 45 it will be 4+5=9 So if i have the number 10 it will be 1+0=1 So if i have the number 5 it will be 5 But If i have the num 29 it will not be 2+9=11 but 2 (1+1) or i have the num 28 it will not be 2+8=10 but 1 (1+0) Thats because i always want as result one digit number so if the firs results isnt one digit number it has to continue the splitting ! Thanks in advanc ...

change customer purchase history
Is there a way to change customer purchase history? We have several transactions that were rung under the incorrect customer. It would be nice if we could assign those transactions to the correct customer. MP The only way of doing this would be to run a sql query. In order to do this you need an understanding of how to write sql queries as well. If you have an understanding, you can use [Transaction].CustomerID and [Transaction].TransactionNumber to assign those several transactions to the correct Customer. Otherwise, I would suggest contacting your RMS Partner to help with this. Rob...

Excel 2002 Crashes When Enabled Macros & on Save Event
we have excel worksheet with macros, we validate data using vba macros regular expressions and update cell colors based on validaty of the data. in some of our machines on certain user profiles whenever we save it excel is crashing. we changed font color & background, it seemed to work little, but entering into debug mode at message box level. Not sure what causing this problem, same spreadsheet works in other machine with the same version? Please let me know what areas I can look into to fix this problem. ...

Click a button and add info to where the cursor was on the last text box.
How do I find out where the cursor was last in a text box. For example I will use the | for the cursor in the following: Bob has purchase | from us last week. Now I want to have a list box with all our items in it so the user can click one of the items and it gets inserted right where the cursor was. So the text box should end up looking like: Bob has purchase shovels from us last week. Hi, something like this. Forget about trying to find where the cursor was last in the textbox. After the user has made their selection from the listbox set the value of the textbox. Me.txtbox = &...

Changing the Password for the Mail Services Account
Is there an issues with doing this on an exchange 5.5 server. I think not, but figure I would ask first. Thanks, SRG Actually, yes there are issues. In addition to changing the password from within User Manager, you would also need to change the password on all of the Exchange Services in the Services applet (Control Panel, Services) as they run under the context of that user account, and also in Exchange Admin on the properties of the Configuration container, Service Account Password tab. -- Ben Winzenz Exchange MVP "SRG" <mlaufer@srg.com> wrote in message news:ae...

How do I stop a cc email to myself from going to deleted folder
Sometimes when I send an email, I want to keep a copy ofr myself. When I enter my email address in the cc part, this always goes straight to the deleted folder, even though I have my email listed as a safe sender. Any suggestions? On 13/03/2010 01:53, technochallenged wrote: > Sometimes when I send an email, I want to keep a copy ofr myself. Outlook automatically keeps a copy of every email you send in the Sent Items folder. There is no need to cc yourself. "technochallenged" <technochallenged@discussions.microsoft.com> wrote in message news:D4...