Numbers change in cells when I hit enter

Discuss the spreadsheet application
Post Reply
eddie1261
Posts: 6
Joined: Thu Aug 09, 2018 10:56 pm

Numbers change in cells when I hit enter

Post by eddie1261 »

The weirdest thing. If I enter a 16 digit credit card number, like xxxx-xxxx-xxxx-2836, when I hit enter, the spreadsheet adds 2, so I get xxxx-xxxx-xxxx-2838. Consistently 2. No matter what I format the cell to, it does it. I tried text, number with no decimal places, everything. I have a spreadsheet with all my credit card numbers entered, the open date, the balances, etc... The cells where math and formulae are concerned function fine. When I make a payment, and use =sum(d3:d4) in cell D5, I get teh correct calculation. Just that column where I want the credit card number does this behavior.

Any ideas?
Open Office 4.1.5
Windows 10
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Numbers change in cells when I hit enter

Post by RusselB »

Welcome to the Forums.
Personally I'd never put my credit card numbers into a spreadsheet, but that has nothing to do with your query.
I can't think of any reason why the numbers are being altered as you state.
Do you see the same situation trying a new empty spreadsheet?
If you don't, then you might be best to open a new spreadsheet, and copy your data from the old one to the new one.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
eddie1261
Posts: 6
Joined: Thu Aug 09, 2018 10:56 pm

Re: Numbers change in cells when I hit enter

Post by eddie1261 »

I had to delete the column and insert a new column, and set the formatting to text. That allowed me to reenter the numbers.

I appreciate your concern about security, but my network sits behind a hardware firewall and nobody can get in.
Open Office 4.1.5
Windows 10
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Numbers change in cells when I hit enter

Post by keme »

There is a perfectly sound reason why the number changes. Calc handles numbers to approximately 14 significant decimal digits precision.

Calc is primarily a tool for calculation (the name is a hint ;) ). So, by default calc will try to find an unambiguous numeric interpretation of any cell input. A credit card number is not a number in the calculation sense, but an identifier code. This kind of numeric data should always be handled as text, not numbers. Your solution (formatting range to text before entering data) forces new entries to be stored as verbatim text with no numeric interpretation, and is the best solution we have in Calc.

Also, if you include the separators (hyphens) with your CC number, there is no sensible numeric interpretation, and Calc will "fall back" to textual data. This is not a recommended strategy.

There are a few other issues with numeric ID codes:
  • Some characters are used for special number contexts. Dot or comma is used as a decimal separator. Slash is used as fraction divider and as a separator in dates. Leading/trailing hyphen is minus.
  • Numerically insignificant zeroes (leading zeroes, and trailing zeroes after decimal separator) are truncated.
  • Extracting segments of the code (by text manipulation functions) is not reliable.
Some other cases where "numeric conversion" will commonly cause problems: phone numbers, postcodes, person-IDs (membership #s).
Post Reply