Adding Large Numbers and Dates together results with incorrect sum

[Jamerzzzz]

Hi Forum,
Can someone please validate that this is an issue
I have a column with large numbers stored as text (ex. 10000001000012) and I am concatenating a date column (ex. 45265) into a larger number: 1000000100001245265

Now if i evaluate this concatenated number with the VALUE() function, (ex. VALUE(1000000100001245265)) i get a result of = 100000100001240000

Forum, why is the result ignoring the '5265' portion of the date in the evaluation?
Many thanks!

 

[Alex Blakenburg]

Excel can only handle as a number up to 15 digits. When you contatenate it will turn it into text and it will keep all the details however when you try to use value and turn it into a number it will hit the 15 digit limit.
Typically when you concatenate it the way you have it is to use it as a look up key and keeping it as text works fine for that. Why do you want to convert it back to a number ?

 

[Jamerzzzz]

Thank you Alex, this answered the question, i need it for a lookup key but for a match function looking for matchType = 1 and not an exact match

 

[Alex Blakenburg]

You're welcome. Glad I could help.