Excel formatting option

Question

Im copying in data to a cell, when I copy it, the source info is this.

1000486818242033

it copies as this.

1.00949e+14

When i convert to number or text to columns it changes to this

1000486818242030

How can I fix that?

Answer 1

First set the cell format to text: Format > Cells.. > Number (Category Text)

Next paste it into the cell as text using: Edit > Paste Special... (As Text)

The trick is to ensure Excel sees the data as text all the time - it has a strong desire to convert anything that looks like it might be a number into a number - which in your case causes truncation.

For an external data source

Create an empty spreadsheet go to: Data > Import External Data > Import Data

Now connect your data source and step through the "Text import Wizzard" until you get to "step 3 of 3", here you can set the data type of each imported column. The default is "General", change this to "Text" and all should work fine.