A Find and Replace trick for inserting new text

By Susan Harkins , TechRepublic on June 17, 2010

Microsoft Word

---

A Find and Replace trick for inserting new text

You can't use Word (or any Office application) too long without learning a few Find and Replace tricks. This feature really works hard to help you overcome tedious editing tasks. What you might not know is that you can use this feature to insert text.
For example, if you wanted to add a newly acquired title to your name, you could search for every occurrence of your name and add the title manually. You might use Find and Replace to search for your name, using the full replacement string as follows: John Doe, MCSE. Both methods will work, but there's a simpler way: Use the ^& code in the Replace With value.
The ^& code tells Find and Replace to add the text in the Find What entry to the replacement text. In our simple name example, you'd use the following settings:
Find What: John Doe
Replace With: ^&, MCSE
In such a simple example, it hardly seems to matter, but your task won't always be this simple. You might need to insert text both before and after an existing string or the existing string might be long and unfamiliar, opening the door for typos, which can be hard to troubleshoot in a Find and Replace task.
Using ^& to represent the Find What string is an easy way to insert text and keep the Find What string intact.

Microsoft Excel

---

Use formulas to find multi-column duplicates in Excel

Finding duplicate values in the same column is easy; you can sort or apply a filter depending on the circumstances. Finding duplicates that span multiple columns is a tad more difficult. A sort can work, but then you have to find the duplicate values. So while it's better than no solution at all, it's not a good solution. You probably want a solution that kind of screams out at you Here I am! I'm a duplicate!
Let's take a quick look at a simple example. The sheet below contains a column of dates and a column of initials. A few dates are repeated and a few initial sets are repeated; they represent duplicates within those columns. However, we're interested in records that repeat the same date and the same initials. That's what I mean by a multi-column duplicate.
It's fairly easy to spot the duplicates, rows 8 and 9, in such a simple sheet, but what if you had hundreds or thousands of rows to check?

An AutoFilter will work, but it's a vulnerable solution. In this case, there are five distinct dates. That means a user has to review at least five sets of records to find duplicates. Even then, you have to trust your user to actually spot them. It's not a good solution, especially if you have lots of data.
You might try an advanced filter or even conditional formatting, but both would require so much hoop-jumping that I'm not sure the end result would be worth the fuss, when compared to the easy-to-implement solution that follows:

• Concatenate the columns you're checking.
• Use CountIf() to count the number of combined values.

Let's enhance the sheet above to see how this works:

1. In cell C2 enter the formula =A2&B2. (You can combine more columns.)
2. Copy the formula to C3:C9. Excel uses each date's serial value, but that won't interfere with the technique. However, if your values contain times, it might, depending on how the time values were entered.

3. In cell D2 enter the following formula:=IF(COUNTIF($C$2:C2,C2)>1, "Here I am! I'm a duplicate!","Original")
   
4. Copy the formula to D3:D9. At this point, finding multi-column duplicates is as easy as sorting by column D (although this example doesn't require any sorting).

The IfCount() function counts the number of times the concatenated values occur within the extending range. If the count is greater than 1, the formula returns the string "Here I am! I'm a duplicate!"; when the count isn't greater than 1, the formula returns the string "Original". Only the first occurrence will be identified as Original.
This technique easily adapts to include additional columns. Simply add each column to the concatenating formula (column C in this example). Of course, there are other ways to identify multi-column duplicates in Excel.

Microsoft Outlook

---

Send a message to several people without revealing their identities to one another

When you send an e-mail message to the same group of people, frequently, you probably create a distribution list. Then, you specify the list instead of all the individual addresses. A list is more efficient than specifying each recipient for each message.
A list has a second benefit: It protects the identity of each recipient.
Now, if you happen to be sending just one message to several people but you want to protect their identities, you might create a distribution list, but doing so would be overkill. You don't need a list at all.
If you want to send a message to more than one person while protecting the identify of each recipient, send the message to yourself and enter the real recipients in the Bcc control. Bcc stands for blind carbon copy--it's a throwback to the stone age... I mean... an earlier time when people still used typewriters and carbon paper to send letters. Any recipient entered via the Bcc control will have complete anonymity--no other recipient will see any other names or addresses.
Bcc is a quick and easy alternative to creating a distribution list when you'll never use the list again.
Can't find the Bcc control?
If the Bcc box isn't visible in Outlook 2007, click the Message Options tab and click Show Bcc in the Fields group.