One of the neat things that you can do with Excel is to use it to manipulate text.
If you have a rows of similar data, all of which need to be manipulated in the same way, Excel excels in letting you extract, add, manipulate, and generally modify the text. There are a number of Excel functions that are designed for text manipulation.
In this article, we’ll look at the Concatenate function, which lets us combine the text, and then a shorthand version of the Concatenate function that’s easier to use.
We’ll follow that with extracting values from the text in a cell, using the left, len, find, mid, and right functions.
Let’s start by looking at the starting cell values of our samples. These particular cell values might be typical of a list of attendees at a meeting, or maybe a membership list.
We also see the result that we want to create from these cell values.
One way to have Excel keep an updated copy of another cell’s value, even if it’s a text value is to use the + operator, such as =+B4 which would give “John” as the result.
Unfortunately, that doesn’t work to combine cell values.
If you try to use the plus (+) operator to combine the cell values, Excel will allow you to create the formula =B4+C4+D4. Unfortunately, the result is #VALUE, which means that the result didn’t make sense.
The function that’s designed to be used is the CONCATENATE function which is used like this:
Here, we use it to combine the values. Notice that, in this case, there are two spaces added — if you have specific characters you always want to insert, you can enter them directly as values in the formula.
To add a space between two cell values, use " " where you need it in the CONCATENATE function.
Fortunately, there’s another way to use the CONCATENATE function. Adding the cells didn’t work, but we can do it with an & operator. Notice that the formula includes the additions of two spaces and a comma as fixed values in the formula.
Combining cell values turns out to be an easy step.
Separating text that’s in one call into multiuple cell values is more difficult. In order to do this, we’ll use a number of functions for locating characters and extracting groups of characters from the cell value.
Let’s start by looking at the initial data value we’ll use for the examples.
The first extract is pretty easy. We’ll use the LEFT function to extract the first name. LEFT requires two things — the beginning text value (which is in cell B15) and the number of characters to extract.
We’ll use the FIND function to find the space that is the separator in the name. Find returns the number of the character before the character it’s supposed to find.
Now, we’ll use the MID function to extract the last name.
The MID function is structured somewhat similarly to LEFT — beginning text value, starting point for the extract, and then the number of characters to extract.
So, B15 is our beginning value. The starting point is the same FIND formula that we used in the LEFT example, with 1 added, so we start extracting after the space.
The cute part of the formula is calculating the length of the word Smith. We need to locate the position of the comma, then deduct the length of the first name string, and deduct an additional 1 (for the space between first name and last name).
The final step is to grab the title General Manager, using the RIGHT function. Functionally,it works just like the LEFT function, except that it extracts the specified number of characters from the righthand end of the text value.
So, to figure out how many characters we need to extract, we need to know the length of the text string that’s in B15. Then, we need to know the position of the comma separator, and then we need to subtract 1 for the space after the comma.
We don’t often need to manipulate text, but, when we do, Excel gives us some powerful tools.