Im copying in data to a cell, when I copy it, the source info is this.
it copies as this.
When i convert to number or text to columns it changes to this
How can I fix that?
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.