Mar 17 2022 04:15 PM
I am trying to combine two different cells that contain numbers into one cell and it is bringing over incorrect values. In my example below the first part of the data is in cell B2 and the second part is in C2. When I combine them in D2 they show the correct numbers:
Customer | Invoice | Special Inv No |
66878799 | 100400661319 | 66878799100400661319 |
When I remove the formula from D2 it is converted to text:
When I convert it to number it changes the combination I had to something that shouldn't be:
Customer | Invoice | Special Inv No |
66878799 | 100400661319 | 66878799100400600000 |
Why is this happening and what can I do to fix it? Is there a different formula I should use for combing numbers in a cell? Appreciate any help!
Mar 17 2022 09:40 PM
Excel display only 15 digits (of precision). Any other digit reverts to 0.
Do you have to do further calculations (adding, subtracting, ...)?
If not then leave it as text. Otherwise you may look for other applications or addins which can handle numbers with more than 15 digits.
Mar 17 2022 09:46 PM
Mar 17 2022 10:02 PM
SolutionSo, the invoice number is the criteria, right?
Then you can leave it as text but you hit another problem because SUMIF() makes a type conversion from text to number and the result is not correct.
But there is a workaround:
https://newtonexcelbach.com/2017/12/22/the-countif-bug-and-how-to-avoid-it/
Mar 18 2022 09:09 AM
@Detlef Lewin That worked perfectly!! Thanks so much for giving that additional resource as I used the CHAR(173)& and it worked like a charm! Have a great day and thanks again!!
Mar 17 2022 10:02 PM
SolutionSo, the invoice number is the criteria, right?
Then you can leave it as text but you hit another problem because SUMIF() makes a type conversion from text to number and the result is not correct.
But there is a workaround:
https://newtonexcelbach.com/2017/12/22/the-countif-bug-and-how-to-avoid-it/