Hidden characters in one SQL field paste into 2 columns in Excel

I have a lot of data which from time to time I need to copy directly from sql 2012 into excel 2013
however some fields copy its content across 2 excel columns which is seriously annoying and a problem

I have caught a lot using the update [AllSageComments] set [COMMENTS] =replace(replace([COMMENTS],char(10),' '),char(13),' ')  to take out some but there are clearly some hidden symbols and havent a clue how to find or remove.
 (this copies as 2 columns needs to be 1)
ACCOUNT NAME:      Oxxxx Software Limited
ACCOUNT NAME:      Oxxxx Software Limited

I have the same problem with some dates which copy across to 2 columns in excel when I need them to be one
24/06/2010      23/06/2011  (this copies as 2 columns needs to be 1)

I have copied the data from the field into notepad++ (attached) with the offending symbol (looks like an arrow)

what update statement would I run on the field to get rid of this in particular or any unwanted hidden symbol ?
symbols.txt
Chris MichalczukConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Can you kindly let us know the datatypes of the SQL Table columns which are giving errors.
Kindly request you to cleanse the data in the your SELECT statement by removing unwanted characters and taking only the necessary data which would help you have the values available properly in Excel.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
From your attachment, I'm able to see Tab characters available between ACCOUNT NAME:      Oxxxx Software Limited which is causing the values to get entered into 2 fields.
Kindly use REPLACE(your_column, char(9), '') to fix it out..
0
Chris MichalczukConsultantAuthor Commented:
can I create a common function in Sql that does the following

remove commas from a field
returns ltrim on the field
replaces hidden characters, Tabs REPLACE(your_column, char(9), '')  replace(replace([your_column],char(10),' '),char(13),' ')
leaves only 1 space instead of >1 between words

I am having to do this all piecemeal so w#hould love to do this with just one command ie

update table
set field_name = newfunction(field_name)

this could save so much time
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
If you don't want those special characters in those columns, then you can remove it while storing it in the column itself.
Or else, you can add a double quotes("") before and after the column while fetching values to the excel sheet to have it displayed it in a single cell.

If you wish to suggest the current values, then yes, you can replace all those special characters and store it accordingly in the column.
Kindly let us know which method you prefer so that I can assist you with the scripts accordingly..
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.