Very large numbers automatically rounded down to nearest thousand?

[WSBirch]

Good morning,
I'm working in MS Excel for Office 365 (MSO 16.0.10730.20344) 64-bit.

I'm copying large numbers over from a table to a new sheet. They're all numbers stored as text.
I can convert them into numbers. They're all different numbers stored as text, but when I convert them back to numbers, they're all suddenly the exact same number... Even if I just write out the number as a number, it changes into a new number.

When converting from number stored as text back to a number, since the digit is so large, it creates an exponent which is fine but when I format that as a number, it simply rounds down to the nearest thousand making all of the numbers I have equal to the same number. Even if I just write out the number without the preceding double zeros as 100728680042858447, excel immediately changes it to 100728680042858000. It's rounding down to the nearest thousand?

Number Stored as Text ex 1: 00100728680042858447
Number Stored as Text ex 2: 00100728680042858384
Number Stored as Text ex 3: 00100728680042858004When converted back to a number they all show: 100728680042858000

I'm trying to perform a COUNTIFS with these values, but huge chunks of values are being counted as the same value when they're not the same value at all. Excel is just rounding them all down removing the digits that're making them unique values.

Any way to turn this off or get around it?

Thank you!

 

[Fluff]

Xl only displays numbers to 15 significant digits, so you I think will need to keep them as text.

 

[WSBirch]

Dang. If that's the case, I should be able to truncate the first 5 digits or so and use that number instead?
Thanks for the quick reply.

 

[Fluff]

That should work as long as the first 5 digits will always be same.

 

[kweaver]

What is your count trying to accomplish? Can you keep the text representation of the numbers and use that in the countifs?

 

[WSBirch]

That should work as long as the first 5 digits will always be same.

Yep. The first 8 or so digits will be the remaining the same for a very long time, but reducing it down the maximum 15 length would ensure a safer approach and shouldn't have any issues. Thanks!

What is your count trying to accomplish? Can you keep the text representation of the numbers and use that in the countifs?[/QUOTE}

I do actually need to use numbers, or rather want to. I have a large list of numbers that I'm trying to count the number of unique values based on a few different criteria without having to use arrays or pivot tables. I do actually have the same value listed about 5 times, but because apparently excel stop at the first 15 unique characters of a number then rounds it down, it's showing that I have more like 70 of the same value counted instead of the 5 I'm expecting.

 

[WSBirch]

Actually, I'm just going to split the large number into two columns. One for the first 5 digits and a second for the remaining 15 digits. If need-be, I'll just reference the first 5 digits if I ever need to, but for my application I don't think I will.

 

[Fluff]

You're welcome & thanks for the feedback

 

[kweaver]

Actually, I'm just going to split the large number into two columns. One for the first 5 digits and a second for the remaining 15 digits. If need-be, I'll just reference the first 5 digits if I ever need to, but for my application I don't think I will.

That sounds like a good move.