The Value of Excel Text Functions

An Essential Skill for a Mail Merge Project

The key to a quality mail merge operation is to prepare the data in your source file before opening Word to place the merge file placeholders.One of the steps to prepare data is to change the text into proper case. Text that is in all one case (like all caps) or mixed case looks unprofessional. Text strings with extra spacing should also be corrected.

Let’s face it, a lot of projects today start with a database query that is exported to a spreadsheet. Data entered into database applications (especially the old ones), or even typed into a spreadsheet, is notorious for having errors that would not look good on a mailing label. Correcting the data “by hand” is tedious and time consuming. Thankfully, Excel has text functions to help us with this task.

The three most useful Excel text functions for preparing your data for a mail merge are PROPER, CONCATENATE, and TRIM. With these functions we can “scrub” and format “dirty” data from a database export before using it for a mail merge project.

The PROPER Function

The PROPER Function is essential for standardizing text that is in all caps, lower case, or mixed case. Let’s face it, some databases have dirty data.

The Proper Function, “Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.” (MS Office Support)

The CONCATENATE Function

I have found that it’s also easier to place one field placeholder for the name, which has been Concatenated and “scrubbed”, rather than placing the individual "un-scrubbed" fields that make up the name on a letter or mailing label.

The CONCATENATE Function is what allows us to combine the content of several cells together into one cell. We can layout our data in to separate cells so we can sort and filter. Use the CONCATENATE Function to combine the parts of the name into one cells. This makes setting up the mail merge field placeholders a lot easier.

The Concatenate Functions, “The CONCATENATE function joins up to 255 text strings into one text string. The joined items can be text, numbers, cell references, or a combination of those items. For example, if your worksheet contains a person's first name in cell A1 and the person's last name in cell B1, you can combine the two values in another cell by using the following formula:

=CONCATENATE(A1," ",B1)

The second argument in this example (" ") is a space character. You must specify any spaces or punctuation that you want to appear in the results as an argument that is enclosed in quotation marks.” (MS Office Support)

The TRIM Function

The TRIM Function is necessary to clean up the resulting text string. It’s not uncommon for cells with information like title, middle name or suffix to be empty. The resulting text string will have extra space which will have to be trimmed.

The TRIM Function, “Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.” (MS Office Support)

The MS Office support page also has a warning that the TRIM Function alone may not remove any non-breaking spaces that are in the text string. They recommend that we use the TRIM, CLEAN and SUBSTITUTE Function in combination to completely clean the text string. In most cases the TRIM Function alone should be good enough. If it’s not then take the time to use the CLEAN and SUBSTITUTE function.

Nested Functions

You can apply each of these functions to the text string in a different cell column or you can nest them. This means that the argument for a function may contain another function inside it. This allows you to combine the calculation of multiple functions into one cell and one operation. The resulting formula would look like:

=TRIM(PROPER(CONCATENATE(A1," ",B1)))

Value for Mail Merge Operations

The key to a quality mail merge operation is to prepare the data in your data source before opening Word to place the merge file placeholders. Text functions are a great tool for helping you to prepare your data more efficiently.