4. Working with Text

Introduction

Imperfect data and inflexible CRMs are unfortunate realities in our industry. It can often be difficult to get the data you need in the exact format you need it to do your work. Most of our systems are pretty good at making sure the numbers are right, but they can make it difficult to deal with text values. Common issues include:

• Splitting source code-level data out by each individual character of the source code
• Getting the first (or last) N characters from some text (e.g., a source code)
• Combining text from multiple cells into one (e.g., merging a List Name and a List Select)

In this guide, we will cover how to use Excel’s built-in text management operators and functions to get your job done more efficiently.

1. Getting the Nth character from some text

The most common use case for this operation is splitting out a source code by its positions. In our example, we have the following revenue report for a set of Mid-Level fundraising source codes. We want to filter our data by channel, but the CRM doesn’t output a field for that. However, we know that position 5 of the source code denotes the channel, so let’s add a blank column called Channel and see what we can do:

Excel provides a function that does exactly what we need, =mid(). This function takes three arguments, parameters that tell the function what to do:

• text: the text value that we want to extract a portion from (in this case, the source codes in column A)
• start_num: the position in the text where the extraction should begin (counting starts at 1)
• num_chars: the number of characters to extract

We know that for our first source code, we want to extract one character (the 5th character) from the text in cell A2, so our formula is =mid(A2,5,1):

Now just fill down that formula to the rest of rows and we’ve got our column for channel!

2. Getting the first/last N characters from text

Sometimes we need to extract the first or last N characters from a text field. This can be done with the mid() function (plus some creativity to get the last N chracters), but thankfully we don’t need any creativity. Excel provides two functions that do exactly what we want, left() and right().

These functions return the leftmost and rightmost, respectively, characters from some string of text. They each take two arguments, parameters that tell the function what to do:

• text: the text value we want to extract the characters from
• num_chars: how many characters we want to extract (techinally optional, leaving this blank will cause Excel to use the default value of 1)

In the data below, we have source code level results from three campaigns that we mailed. We want to add the Campaign Code and Segment Number to this data, defined as:

• Campaign Code = first 5 characters of the source code
• Segment Number = last 3 characters of the source code

Using left() and right() for this task is quick and easy. For the campagin code, we want the first 5 characters so our formula for the first source code is =left(A2,5). For the Segment Number, we want the last 3 characters so our formula for the first source code is =right(A2,3). Enter these values and fill down:

3. Combining text from multiple cells

Excel provides a simple operator, &, to combine text from different cells into one value. There are many reasons we may want to do this in our work, but one example is combining a List Name and List Select into a single value. In our example, we have these two list-related fields and we want to combine them into a new field called “List With Select”:

Using & is straightforward. All you need to do is include it in a formula by placing it between each cell reference or literal text value that you want to combine. We want to combine List Name and List Select, with a space between them. Our formula for the first row of our data will be:

=A2&" "&B2

This formula tells Excel, “Take the text that’s in A2, add a single space to it, and then add the text from B2 to the end.” Note that any literal text (like the space in our example) that you want to combine must be enclosed in quotation marks. Let’s add our formula and fill down: