From: Arthur Moraitis Arthur Moraitis on 6 May 2010 05:48 In the past I was able down load a number from a data file (credit card transaction numbers for internal audit part of my job) I would have to change the number to a value for the vlookup to work properly. 15 character eg 999563256923654 would be translated to come out as 9.99563E+14 WHEN CONVERTED TO A VALUE. The problem I have now that the transaction numbers have increased one digit to 16 characters transferring it to a value is rounding up or down the last number to eg 10000000000001656 conveting to a value (1e+16) and when hover over the cell would find that the last number (6) has been rounded up to 0 this is very frustrating as it is not aligning with the right credit card transaction. Can someone please explain why this is happening. Is it something in my settings in excel. Your help will be appreciated. Arthur
From: Dave Peterson on 6 May 2010 08:15 Excel only keeps track of 15 significant digits. Maybe you can bring the field in as Text  but that means that your table will have to have those matching values as Text, too. Arthur Moraitis wrote: > > In the past I was able down load a number from a data file (credit card > transaction numbers for internal audit part of my job) I would have to change > the number to a value for the vlookup to work properly. 15 character eg > 999563256923654 would be translated to come out as 9.99563E+14 WHEN CONVERTED > TO A VALUE. > The problem I have now that the transaction numbers have increased one > digit to 16 characters transferring it to a value is rounding up or down the > last number to eg 10000000000001656 conveting to a value (1e+16) and when > hover over the cell would find that the last number (6) has been rounded up > to 0 this is very frustrating as it is not aligning with the right credit > card transaction. Can someone please explain why this is happening. Is it > something in my settings in excel. Your help will be appreciated. Arthur  Dave Peterson
From: Gary''s Student on 6 May 2010 08:17 Once the transaction number exceeds 15 digits, you should you should stop trying to convert to a value and starting treating the digits as Text. This will allow the VLOOKUP() to function poperly. For example, if D1 thru E5 contains: 6841145576479986 Larry 4170432082457810 Moe 7321426544703675 Shep 4996312194002342 Curly 2963555685111655 Wilber and if A1 contains: 4170432082457810 then =VLOOKUP(A1,D1:E5,2,FALSE) will correctly display Moe  Gary''s Student  gsnu201002 "Arthur Moraitis" wrote: > In the past I was able down load a number from a data file (credit card > transaction numbers for internal audit part of my job) I would have to change > the number to a value for the vlookup to work properly. 15 character eg > 999563256923654 would be translated to come out as 9.99563E+14 WHEN CONVERTED > TO A VALUE. > The problem I have now that the transaction numbers have increased one > digit to 16 characters transferring it to a value is rounding up or down the > last number to eg 10000000000001656 conveting to a value (1e+16) and when > hover over the cell would find that the last number (6) has been rounded up > to 0 this is very frustrating as it is not aligning with the right credit > card transaction. Can someone please explain why this is happening. Is it > something in my settings in excel. Your help will be appreciated. Arthur

Pages: 1 Prev: Time format 1500 become 15:00 Next: Excel Drop Down lists 