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.

1. Open a new Excel spreadsheet
2. Find the column that corresponds to the SQL column with string data that looks like number data
3. Right-click on the column and select “Format Cells…”
4. On the Number tab, select Text as the Category
5. Click OK
6. Back in SSMS, select the top-right corner of the grid, then right-click it
7. Select “Copy with Headers”
8. 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.)