This evening I was trying to take a few Exchange Server distribution groups and import their membership to Excel. There’s probably a way to script this but the method I used was to expand the distribution group membership in Outlook, then copy and paste the contents to a text editor before reformatting for Excel. The problem was that I wanted to move from a list of names separated with semicolons to a vertical list of names separated by line breaks.
Using Word (2007) as my editor, I tried to replace ; with a line break copied and pasted from another document but that didn’t work. It turns out that there is a method to replace using control characters though, as described in a Microsoft help and how-to article about finding and replacing text.
For my situation, I needed to type ^p (or ^13) as the replacement for ; but other options include:
| Find/replace | Type |
|---|---|
| Paragraph mark (¶) | ^p (except with wildcards) or ^13 |
| Tab character | ^t or ^9 |
| ASCII character | ^nnn where nnn is the character code |
| ANSI character | ^0nnn where nnn is the character code |
| Em dash (—) | ^+ |
| En dash (–) | ^= |
| Caret (^) | ^^ |
| Manual line break | ^l or ^11 |
| Column break | ^n or ^14 |
| Page (when replacing) or section break | ^12 |
| Manual page break | ^m |
| Non-breaking space | ^s |
| Non-breaking hyphen | ^~ |
| Optional Hyphen (¬) | ^- |
These may be useful to know – and there are more find and replace options in the article, including wildcards.
So useful in fact that they include all this in the Find/Replace dialog: click “More>>” and click the “Special” button.
…and so well hidden that I didn’t notice it and Microsoft’s own help text doesn’t appear to mention it!
Thanks for the tip Duncan… had I realised that, it would have saved me from writing this post in the small hours of this morning :-)
I’m sure Dave Saxon will be perplexed that you didn’t use PowerShell?? ;-)
Hi Adam – I’m sure he would :-) Unfortunately, fantastic as PowerShell is, I’m not using it enough to be that comfortable with it… I think I need another one of Dave’s courses.
Hope all is well with you now you’re back down under.
Cheers, Mark