Add Apostrophe to Output 1

[IanWaterman]

I have to export some telephone numbers to Excel, but when I do that excel sees it as a numbers and removes the leading zero

I am want to try a couple of solutions

I can pad the number with a space and that allows the leading zero, but that is not Ideal as number now looks like

01234 678987

With Excel if you pad string with leading apostrophe it treats it as a string and the apostrophe is invisible, but enclosing ' in double or single quotes does not work

I have tried

select
MAINCONTACTNUMBER, -- this loses leading zero
substring(MAINCONTACTNUMBER, 1,5)+' '+substring(MAINCONTACTNUMBER, 6,25) MAINCONTACTNUMBER2, -- works but not ideal
"'"+MAINCONTACTNUMBER MAINCONTACTNUMBER3 -- this does not work
'''+MAINCONTACTNUMBER MAINCONTACTNUMBER3 -- neither does this
from Table

Thanks

Ian

 

[simian336]

Try

Select char(39)+MAINCONTACTNUMBER

Simi

 

[IanWaterman]

That did the trick but still does not work as expected when exported to Excel.

Its the right type of apostrophe but it shows in excel instead of becoming invisible. If I delete and replca e then it becomes invisble, if I add an extra one and then deleted the extra one it works too. Whats that all about!!

Thanks for the solution but it seems excel is a law unto itself.

Ian

 

[simian336]

try Edit>Paste Special>Text

or copy and paste to notepad then copy and paste back to Excel

Simi