Problem while exporting numeric data from ms sql server 2008 to excel

In this article i am discussing a problem with you guys that i faced today. Today i run a query in ms sql server 2008 management studio, query was basically giving the following records from student table

1. Registration_Number
2. First_Name
3. Last_Name
4. Email_Address

Query was

SELECT REGISTRATION_NUMBER,LAST_NAME,FIRST_NAME,EMAIL FROM STUDENT 
ORDER BY FIRST_NAME

Problem was with Registration_Number column, in table its data type was bigint. When i run the that query, copy the result set and then paste into excel then Registration_Number column was giving two problems, mentioned below

1. Some of registration numbers were in unwanted data type for me such as floating type. I had a registration number 1010512500100483 which was showing as 1.01051E+15
2. Secondly, some registration numbers were automatically rounded off, such as 1010512500100505 was rounded into 1010512500100500, moreover in excel it was showing as 1.01051E+15 but when I clicked on the cell then it was showing in address bar as 1010512500100500 instead of 1010512500100505

Now we have two solutions, first one is pretty simple in which your excel cell should have been formatted as text before you pasting the result set. 

Before telling you second and accurate solution, let me tell you methods that i adopt in order to paste the actual result set from sql server to excel without changing the cell data type in excel. 

Methods that i adopt and all were proved fail. 

I converted the data type of registration_number column from bigint to varchar as mentioned below but it was not working.

SELECT CONVERT(VARCHAR,REGISTRATION_NUMBER) AS REGISTRATION_NUMBER,LAST_NAME,FIRST_NAME,EMAIL FROM STUDENT 
ORDER BY FIRST_NAME 

I converted the datatype of registration_number column from bigint to varchar and concatenated space as mentioned below but it was not working.

SELECT CONVERT(VARCHAR,REGISTRATION_NUMBER)+' ' AS REGISTRATION_NUMBER,LAST_NAME,FIRST_NAME,EMAIL FROM STUDENT 
ORDER BY FIRST_NAME 

But the following query proved successful as i achieved my target which was copying the query result set and pasting it into excel sheet without formatting any excel cell.

SELECT CONVERT(VARCHAR,REGISTRATION_NUMBER)+CHAR(160) AS REGISTRATION_NUMBER,LAST_NAME,FIRST_NAME,EMAIL FROM STUDENT 
ORDER BY FIRST_NAME 

That's it. Cheers!!!