Tuesday, June 15, 2010

How to replace ENTER with comma in MS Word

I want to share with you very useful tip I got from my college.
There are cases when you need to generate a comma separated
string for 'IN' clause in a Select statement. For example:

SELECT    *
FROM    SomeTable
WHERE  SomeColumn IN (xxx,yyyy,www,zzzz)

Let`s assume that you have some values separated by Enter,
like this:

42344
32432
54435
45345
43534
45243

This also can be the result of copying some column values
from a select in the SSMS:



















Ok, suppose you copied these values and pasted them in MS Word
document. The quickest way to replace the Enters with commas
is to use Find and Replace option (Ctrl-H)

















Press Ctrl-H and enter the following:






















Now click on "Replace All" button:












Here are some more useful codes you can use:

TAB CHARACTER
Type ^t or type ^9

ANY DIGIT
Type ^#


ANY LETTER
Type ^$


You can see more codes here

Note: All this above can be also done in SSMS by using Regular Expressions:
See my post about it.
P.S: do you know the way how to do this in Notepad?
I think it is not possible , because the "Replace" dialog box
in Notepad does not work with non-printing characters.
I will be glad to know if I am wrong.

11 comments:

  1. Wow! This is what I call useful info on the web. Cheers!

    ReplyDelete
  2. Hi, These was very useful for me, thanks! I have always wondered what is "enter" "character" when you do replace in Excel or Word.

    ReplyDelete
  3. Hi, this was very useful. Thanks for sharing the info.

    ReplyDelete
  4. Extremely useful tip. Would have never found out that ^p refers to Enter. Thanks.

    ReplyDelete
  5. I Needed this SO MUCH!!! Outstanding!!

    ReplyDelete
  6. thank you so much!! this helped me alot

    ReplyDelete
  7. Nice one, Thanks you

    ReplyDelete
  8. And all these years later this just saved me a LOT of time. THANKS!

    ReplyDelete
  9. This is something I have not seen before. Thanks the replace commas with new lines.
    Regards
    How To Code

    ReplyDelete
  10. you made my life today, hats off

    ReplyDelete