# excel 2010 changing my cell values in number format

Hi Folks, I have a user who has a 16 digit number in a cell that starts out in text format and then he changes it to number format. The problem is though the number changes the last digit to zero for some reason. For example, he has the value 1100329224361331 as a text value and then changes the cell format to number. But the resulting cell value is 1100329224361330.00. The 330 should be 331. Is there a setting somewhere causing this? Thanks!
###### Who is Participating?

EngineerCommented:
The precision which excel works with is limited to 15 digits. The last 1 is the 16th digit which is always truncated and will remain this way. You will have to find some other way to handle this problem.
0

Commented:
Excel only supports 15 significant digits in numbers - if you have a 16 digit number like that you need to store it as text to retain accuracy

regards, barry
0

EngineerCommented:
If you can give more detail on how and why the value is changed from text to number and then back then maybe we can help find a solution.
0

Author Commented:
So the value of the cell is changed as well, not just what is viewed? Also, is there any way at all around this while using a number format?
0

Commented:
Why can't you keep it as text? I don't believe there's an easy way round it.....

regards, barry
0

Author Commented:
well the number just starts out in text and then just manually right clicking, format, and then changing it to number to get the change to the format made. As to why doing it this way, I am not sure, but that is what they need it to be. That is the process though.
0

Author Commented:
The idea is a VLOOKUP was returning inaccurate results in text format, so we thought number format might work better since these are of course numbers.
0

EngineerCommented:
You can use a converter within the vlookup to convert the text to number instead of modifying the cell.

0

Author Commented:
We didn't notice a conversion tool within it. Can you be more specific as to where it is?
0

EngineerCommented:
If the number is in A1 you can use something like

=vlookup(A1*1,........

The *1 will change the text to number.
0

Commented:
...but as soon as you multiply by 1 the lookup value loses precision (you'll still lose the 16th digit).

What are you looking up against? for exact matches with 16 digit numbers they should all be text-formatted (lookup value and lookup range). You can't match number to number unless you are happy to match only with the first 15 digits....

regards, barry
0

Author Commented:
honestly folks, I was able to do this in Access for the user since that is far better at this sort of thing. But thanks for all the help folks!
0

Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for mrosier's comment http:/Q_27393600.html#36958467

for the following reason:

I just changed programs since I couldn't do what was needed in Excel
0

EngineerCommented:
"Not possible" is a valid answer. So it would be appropriate if you award points.
0

Author Commented:
oh ok, understood. No disrespect was intended to anyone, I just thought that was how it was meant to be handled. My apologies, folks.
0

EngineerCommented:
Thanks for understanding and thanks for the points. I hope you are able to get over with your problem efficiently.

Saqib
0

Author Commented:
Access made it easy once it was fully explained what needed to happen. Thanks again Saqib!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.