Share via

Why does excel set everything to scientific notation. I set columns as text/number makes no difference. When I paste IDs from another system they are put into scientific notation. Why?

Anonymous
2024-01-19T00:19:52+00:00

Why does excel set everything to scientific notation. I set columns as text/number makes no difference. When I paste IDs from another system they are put into scientific notation. Why? It drives me crazy its just such an obvious design flaw.

Microsoft 365 and Office | Excel | For business | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Bob Jones AKA CyberTaz MVP 435K Reputation points
    2024-01-19T02:21:05+00:00

    There's no need for a Custom Format & as you've found the pasted content is treated as a Value if it strictly consists of numerical digits even if the cell is preformatted as Text.

    Format the cell(s) as Text first. To Paste click the right edge of the Paste tool then either:

    • select Match Destination Formatting, or
    • paste then select that option from the Paste Options button.

    If you do much of this you may want to assign a keyboard shortcut to the Match Destination Formatting command.

    As another option go to Excel> Preferences> Edit - Automatic Data Conversion to clear the check for Keep first 15 digits pf long numbers and display in scientific notation.

    2 people found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-01-19T01:52:43+00:00

    Excel automatically converts numbers to scientific notation when they are too large or too small to be displayed in the cell. This is a default behavior of Excel and cannot be turned off.

    1.You may set the column as text format first, then paste IDs from another system. Then it should work.

    Could you share us how long is your IDs?

    1. You may set Custom format as this one with same count of your id numbers if your ID number is less than 15.

    0000000000000000000

    0 comments