Problems converting text to numbers

[Masun]

Hi all,

Got stuck here. I try to convert the text formated numbers to numbers. When I try to convert it to numbers the three last digits changes to 000. I dont know why and hope for some assistance here.

Please see attached file.

[AlKey]

Because there is a maximum of 15 digits. It is unlikely that these numbers will be involved in mathematical calculations and therefore should be stored as text.

[Glenn Kennedy]

Excel has a 15 digit limitation when it comes to numbers, and that after 15 digits, it simply replaces everything with 0's. So... Are these numbers, or "composite" codes? If so, you should be able to split them and use the components as numbers

[6StringJazzer]

Excel has a built-in precision limit of 15 significant digits. Your numbers have 18 so the last three digits are lost converting to numeric.

Are you really dealing with numbers in the quadrillions, or is this some kind of serial number? Do you need to do arithmetic on them? If not, you are better off leaving them as text, depending on what operations you need to perform.

[Masun]

Hi,

Thanks for your answers. It愀 just whats in my bank accounts...

I have about 14 K of rows where i need to compare one column with another. I need to count how many is in one of the columns but not in the other. I惻l figure something out now that i know about the limitations. When i first made the count it looked really weird but now i understand why. Thanks!

/Masun

[Glenn Kennedy]

If you have bank accounts with balances containing 18 digits (even in SEK) that makes you VERY wealthy.... Friend... But I guess it the account numbers... not their balance.

[AlKey]

Thank you for the feedback!

[Masun]

Glenn,

damn...i forgot to write that it was the balance....which made that joke even more bad...

Its a serial number of some kind...

Thanks again for sharing your knowledge!

/Masun

[Glenn Kennedy]

What a shame... There was me on the Ryanair site, booking a cheap flight, too....

Thanks for the Reputation. Appreciated!

[6StringJazzer]

I don't understand if you solved your problem....it sounds like you can leave it as text and still do what you need to do.

[Masun]

Hi,

I solved it. But i guess there is a way without the helper columns i added. In my data set only the last 7 digits are unique. I made three new columns, two with RIGHT function and the third with COUNTIFS. See attached file. How would a better solution look like?

[Glenn Kennedy]

Hi. It depends what you want to see. You could use conditional formatting...

[Masun]

Hi,

Conditional formatting is one way. Is there a way to do it without the helper columns? Just one column with the results?

[Glenn Kennedy]

In your example, this works...
=RIGHT(A2,7)=RIGHT(C2,7)

[Masun]

Hi,

I am not sure I am following here. That compares row by row? After the first mismatch there will alywas be mismatches...I want to know which numbers in A that do not exist in C. Or did i misunderstand you now?

[Glenn Kennedy]

Ignore this post of mine. Head not working...

[Glenn Kennedy]

In your example (whether intentionally or otherwise), the match for column A was always in the same row in column C. In those circumstances, the formula I suggested does work. However, i guess that's a very artificial situation. Something else is needed. So, why not just use the entire 18 digit number (as text). The green cells show the simple situtation as in your example (same rows). However the tan and pink cells show that this works, even when the matching values are NOT in the same row.

[Masun]

Hi,

Perfect. Thanks!

[Glenn Kennedy]

You're welcome...