How to Search For and Use Text Strings in Excel

A viewer asked for my help in creating a report that will group his customers’ email addresses by their “domain name.” For example, he wants to be group all customers with gmail addresses . Likewise, his customers’ yahoo mail, aol.com accounts, etc.

Pivot Table Email Domains

I immediately decided to use two powerful Excel tools to produce this report:

Text-to-Columns to create two columns (name and domain) from one text value (the email address).
Pivot Table Report to group and present customer emails by domain name.

Both tools work great and I produced the report with only a few mouse clicks!

The response that I got from my viewer was, “That’s great Danny. But …”

It turns out that the viewer wanted to have the Domain Names as the Field Headers and to show each customer’s email address as a row going down vertically in the corresponding field. To give my viewer the report that he wanted, I used these tools and techniques:

I returned to the Pivot Table to copy the unique list of domain names.
I used Paste Special – Transpose to convert the vertical list of domain names into a horizontal row of Field Headers.
I began to build my “Mega-formula” by taking “baby steps” – i.e. I created one formula and then “nested” it inside additional formulas and functions.
For the first “baby step” formula I used the SEARCH() Function.
Next, I “nested” this formula as the “logical test” inside an IF() Function.
In order to “hide” all of the formula error signs, I nested both of these functions inside the NEW IFERROR() Function.
IFERROR() was introduced in Excel 2007 and I now consider it to be “indispensable!” This function, alone, makes upgrading to either Excel 2007 or Excel 2010 a “nobrainer” decision!

I enjoy answering questions from my viewers. Many of these questions can be answered from one of the lessons on my DVD-ROM, “The 50 Best Tips for Excel 2007.” For the rest, I try to create a video lesson that demonstrates how to get the answers that you – my viewers and customers – are looking for.

Keep your questions and suggestions coming! I enjoy receiving feedback from you!

I invite you to subscribe to my RSS Feed on Feedburner to be automatically notified whenever I publish a new Excel Video Tutorial.