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!
mrosierAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor 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
 
barry houdiniConnect With a Mentor 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
 
Saqib Husain, SyedEngineerCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
mrosierAuthor 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
 
barry houdiniCommented:
Why can't you keep it as text? I don't believe there's an easy way round it.....

regards, barry
0
 
mrosierAuthor 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
 
mrosierAuthor 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
 
Saqib Husain, SyedEngineerCommented:
You can use a converter within the vlookup to convert the text to number instead of modifying the cell.

0
 
mrosierAuthor Commented:
We didn't notice a conversion tool within it. Can you be more specific as to where it is?
0
 
Saqib Husain, SyedEngineerCommented:
If the number is in A1 you can use something like

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

The *1 will change the text to number.
0
 
barry houdiniCommented:
...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
 
mrosierAuthor 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
 
mrosierAuthor 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
 
Saqib Husain, SyedEngineerCommented:
"Not possible" is a valid answer. So it would be appropriate if you award points.
0
 
mrosierAuthor 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
 
Saqib Husain, SyedEngineerCommented:
Thanks for understanding and thanks for the points. I hope you are able to get over with your problem efficiently.

Saqib
0
 
mrosierAuthor 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.

All Courses

From novice to tech pro — start learning today.