Excel is truncating zeros in data set

**michonna1474** · 07-06-2012, 03:58 PM

I have a set of data that contains 11 characters. When one of the characters is a letter, and I copy the data and I copy it from another source and paste it into a cell on a blank spreadsheet, the data entire 11 digit string remains intact. However, if the 11 character string is all numbers and the last two digits of that number are zeros, the data is changed into a scientific format number and the last two digits are removed from the cell.

ex1: copied as (123456789D00) and pasted as (123456789D00) - Correct
ex2: copied as (123456789900) and pasted as (1.234567899) - Incorrect

How can I get Excel to stop doing this! 45% of these ID fields are numeric only and are therefore truncating. Doesn't matter if the data is copied from an Outlook email, Access 2007 or even word, once its pasted into Excel, it automatically defaults to this scientific Format

**ChemistB** · 07-06-2012, 04:01 PM

Format the cells as Number with 0 decimal places

**Ace_XL** · 07-06-2012, 04:01 PM

The last two digits are still intact. Just change the format of the cell to a number format with zero decimal places.

Also, please change the title of the thread. Excuse my humour, but you dont want silly people searching for nuts on the web to land up on the forum

**Cutter** · 07-06-2012, 04:04 PM

Edit: OP responded to thread title change.

**michonna1474** · 07-06-2012, 04:17 PM

OK, I changed the title.

Format changes are not fixing the problem. I have already tried formating the cells as text, general, number with no decimals, etc. However, when I paste the data, it automatically converts back to scientific format. Has anyone else experienced this and have a work around. Also, the two zeros appear to be deleted as they do not display in the formula bar when I highlight the cell.

**Ace_XL** · 07-06-2012, 04:19 PM

Pre-format the destination cells as 'number with no decimals' format.

While pasting (presumably from and external source), use paste special --> unicode text.

**michonna1474** · 07-06-2012, 04:24 PM

@Ace_XL I tried this and it pasted the data without the decimal. However, it still removed the 2 zero's at the end of my data set. I need the full 11 digit ID to be retained. So This suggestion gets me half way there, but I still need a way that retains my last two digits.

**Cutter** · 07-06-2012, 04:29 PM

Try preformatting the destination range as Text. Since some of the data has a letter in it then all of the data can be text, or is that a problem?

**michonna1474** · 07-06-2012, 04:54 PM

@Cutter Yes they all can be formatted as text. I prefer text. But the preformat is being changed to scentific when the paste occurs. I am copying data in a spreadsheet forma from an email and pasting it directly into my excel spreadsheet.

**Cutter** · 07-06-2012, 04:59 PM

I copied from your original post and pasted into a cell and got the same SN result. But when I preformatted as text and pasted as "Match destination formatting" I got the correct result.
The right click menu (in 2010 version) has 2 options for pasting - the one I mentioned above and "Keep source formatting". The keep source overrides the text formatting and produces the SN result.