[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

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!
0
mrosier
Asked:
mrosier
  • 8
  • 6
  • 3
2 Solutions
 
barry houdiniCommented:
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:
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
 
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 8
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now