 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2007data gets rounded up !!!
I have devised a workbook where I enter the credit card details of the transactions received at my hospital for people who pay by credit card. In this there is one column where I enter the credit card number. This cell I have given a general format, text format and a number format. But my tragedy is that the last digit gets automatically rounded up. I don't understand this. What do I do ? Thanks Dr Alok Modi MD Hi A number can only have 15 digits in Excel. Precede the entry with a single quote to force it to be text, or pre-format the cells as text BEFORE entering the CC number.  SignatureRegards Roger Govier >I have devised a workbook where I enter the credit card details of the > transactions received at my hospital for people who pay by credit [quoted text clipped - 10 lines] > > Dr Alok Modi MD > Hi > [quoted text clipped - 18 lines] > > Thanks all of you, I did custom format as you have mentioned, I did text format, nothing works. Don't laugh at me when I say that text format does not work, Can I upload the file so you guys can see for yourself ? Thanks Dr Alok Modi MD Hi Applying a format after you have entered data, will not change the data in the cell. You would need to re-enter the values. If you format a cell as Text, then enter 01234567890123456 it will take all 16 digits and will retain leading zeros. SignatureRegards Roger Govier >> Hi >> [quoted text clipped - 26 lines] > Thanks > Dr Alok Modi MD Thanks but I have done all that. See for eg if I were to enter a credit card number 1234 1234 1234 1234 it gets entered as 1234 1234 1234 1230. Even if the credit card number were 1234 1234 1234 1237 it would still get entered as 1234 1234 1234 1230. Excel rounds the last digit as zero no matter if the last digit of the credit card number that you enter is less than five or greater than five. Please help me as my work has come to a standstill and I can't work this problem out . I am sure this is a useful question as this problem would be common to many other users of excel. Dr Alok Modi MD Not if you enter the number preceded by an apostrophe which Excel reads as anything following is text '1234 1234 1234 1234 Or pre-format the cell(s) as Text. Gord Dibben MS Excel MVP >Thanks but I have done all that. See for eg if I were to enter a credit card >number 1234 1234 1234 1234 it gets entered as 1234 1234 1234 1230. Even if [quoted text clipped - 6 lines] > >Dr Alok Modi MD Thanks Gord That ! did the job. But fromatting the text as text converts the data as something like this 12E + something. Thanks Dr Alok Modi MD I cannot replicate your problem using a pre-formatted cell or by preceding with an apostrophe. I can if I enter the data then format to text Do not re-format after you have typed in the text. Pre-format prior to entering the text 1234 1234 1234 1234 If you are entering the spaces as above you don't even need to pre-format or add the apostrophe Gord >Thanks Gord >That ! did the job. But fromatting the text as text converts the data as >something like this 12E + something. > >Thanks >Dr Alok Modi MD Thanks a lot Gord. My problem got solved like you did. Even if I did enter the data and then gave the format as text, I was getting the data appaering as 12E+ something. So if I were to double click on that cell, it comes back as 1234123412341234. Now only if we had some means of giving a format of 1234-1234-1234-1234, which is possible if your data type is numbers, but I am not aware of this in text data type. Dr Alok Modi MD Try creating a custom number format... if it's always 12 digit credit card numbers try ####-####-####-#### or if you don't want the dashes just type # 12 times... ############ > I have devised a workbook where I enter the credit card details of the > transactions received at my hospital for people who pay by credit card. In [quoted text clipped - 6 lines] > > Dr Alok Modi MD whoops, i forgot 4 * 4 = 16... should still work though > Try creating a custom number format... if it's always 12 digit credit card > numbers try [quoted text clipped - 15 lines] > > > > Dr Alok Modi MD No sixteen digits is one more digit than 15, and 15 is the maximum in digits if Excel. You cannot use a number format, and will have to enter the identification as a text entry as previously stated. > whoops, i forgot 4 * 4 = 16... should still work though > [quoted text clipped - 17 lines] > > > > > > Dr Alok Modi MD Pre-format cells as Text, or precede with aspostrophe. > I have devised a workbook where I enter the credit card details of the > transactions received at my hospital for people who pay by credit card. In [quoted text clipped - 6 lines] > > Dr Alok Modi MD |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.