Prev: Time format 1500 become 15:00
Next: Excel Drop Down lists
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