Concatenate, Left, Mid, and Right functions allow the user to combine or parse text data across multiple cells.  This tutorial will focus on utilizing these four functions as well as incorporating them into other formulas for efficiency.  Attached is an Excel workbook that includes the necessary raw data to follow along with the exercises below as well as blank columns to facilitate practice.

Concatenate: Combining Text Data

The concatenate function will combine text (or numerical, if applicable) data across multiple cells defined by the user.  One can insert additional characters or spaces as needed to achieve the desired result.  The first practice example will walk through the Insert > Function wizard to create the formula, but simply typing the arguments using proper syntax will lead to the same result if you are more comfortable with that approach.

Go to Insert > Function.  This should bring up the window as seen below and will assist in proper function development.  You can search for a function by typing into the search box or by selecting a category from the drop-down menu.  Either way works fine.  Select the Concatenate function.

This should bring up the concatenate function builder window (image below).  In the space next to ‘Text1,” either type “F3″ or click in cell F3.  Notice that the contents of the cell are displayed in the gray space next to the Text1 box.  This is useful for ensuring that the cell reference for Text1 is as you expected.  For Text2, type a quotation mark, a space, and then another quotation mark.  This means that Text2 is a space.  In a minute you will see why we inserted a space as Text2.  In Text3, either type “G3″ or click in cell G3.  Click OK.

The function result should look like “Donovan McNabb 1999″.  If you were to dissect the function, you will see that Text1 = “Donovan McNabb”, Text2 = ” “, and Text3 = “1999″.  Without assigning Text2 to equal an empty space, the function result would look like “Donovan McNabb1999″.

Note: Anywhere from one to thirty (1 to 30) text strings can be concatenated.

Left, Mid and Right: Parsing Text Data

Left, Mid and Right functions all perform a similar task with only the details being different, so for the purposes of this tutorial we will consider them a family of functions.  These functions essentially allow you to pull a piece of a cell out into its own cell.  Again, we will walk through the Insert > Function wizard, but typing the formula out works just fine.

It is vital to understand one principle prior to working with this family of functions.  In order to properly utilize the functions, you need to assign a number to each character in the cell that you are trying to extract text.  All things, including dashes, spaces, etc, must be counted.  See the example below using “Donovan McNabb”.  This concept will be used in all three functions in this family.

Go to Insert > Function.  As before, search for and select the Left function.  In the space next to “Text” either type “F3″ or click in cell F3.  This specifies the cell from which you want to select a given set of characters.  The next argument, “Num_chars” allows you to indicate the number of characters that you want to extract from the left of the cell.  In this instance, the first name of Donovan is seven (7) characters long, so if you want the whole name type “7″ for the Num_chars argument.  Click OK.  You will notice that the Left function extracts the specified number of characters from the left of the Text cell specified.  So, in this example, we said “Show me the seven characters leftmost in the cell specified.”  The result should be “Donovan”.

The Right function works in exactly the same way except that you are extracting the specified number of characters rightmost in the cell.  It is important to note that sometimes a cell may have spaces after the visible text and therefore must be deleted or else the Right function will consider those spaces as characters.  A sample screenshot of the Right Function wizard window is below.  The arguments used are Text = F3 and Num_chars = 6.  The result should be “McNabb”.

The Mid function follows the same logic in principle as the Left and Right functions, but it can be a little trickier because you must tell Excel where in the string of characters to begin extracting.  The screenshot below shows the Mid function wizard window used to build the example.  The only difference between this Mid wizard and the other two is the argument called “Start_num”.  Start_num is the argument where you tell Excel what position in the “Text” cell (F3 in this example) where you want to begin extracting.  Using “2″ as the Num_chars argument, the function result should be “Mc”.

NOTE: The Start_num you enter should correspond to the number of the first character that you want to return.

Advanced Uses

Now that you understand the basic way in which these functions work upon data, you can get creative by embedding these functions inside other Excel functions.  The sample workbook has a column labeled “Mutli-function” which can be used to practice embedding these functions in larger Excel functions.

The formula currently in the first cell of the Multi-function column combines both a Right and a Concatenate function.  The function returns the rightmost six (6) characters of the column containing player names and concatenates those six characters with a blank space (for separation) and the title of “Passing Stats” to yield a result of “McNabb Passing Stats”.  See the image below for proper syntax.

It is also possible to perform a vlookup function that makes use of one of these text functions.  Example:  you have a need to use a vlookup function but the value that you need to use as the lookup_value is a piece of a larger text string inside a cell.  You can perform the vlookup in one of two ways:

  1. Insert a new column and make use of the appropriate text function (Left, Mid, Right) to extract the value you need as the lookup_value.  Then execute your vlookup function using the values in the new column as the lookup_value.
  2. Use the text function as the vlookup_value argument to your vlookup function.  Example: =VLOOKUP(LEFT(G3,6),Range,3,false)

These are just two examples of advanced uses of the text functions discussed in this tutorial.  As always, creative uses of these functions are encouraged.  Good Luck!

Interested in learning more? …check out the tutorials page for more Microsoft Excel techniques.