The problem arises when you have a column with character strings that look like numbers. Looking like a number isn’t a problem in and of itself, unless the value starts with the character “0”. Excel will try to treat the column’s values as a number, and therefore eliminate any leading 0s.
The trick is to set up the column properties in the spreadsheet before you copy the values into it.
- Open a new Excel spreadsheet
- Find the column that corresponds to the SQL column with string data that looks like number data
- Right-click on the column and select “Format Cells…”
- On the Number tab, select Text as the Category
- Click OK
- Back in SSMS, select the top-right corner of the grid, then right-click it
- Select “Copy with Headers”
- In Excel, paste the data into the spreadsheet
Now the column should properly maintain any leading 0s on the strings-that-look-like-numbers.
(I rarely need to do this, but when I do, I inevitably end up searching online for the solution. May as well document it here so that I can find it when I need it.)