Combining two or more text strings into one string Back to List of Topics


Understanding concatenated cell formulas

Don't be scared off by the terminology. The simple definition of concatenation means adding two or more cells together, except you are dealing with text rather than numbers. If your first name is in a cell and your last name is in another cell, as below, then the formula to put your first and last name in a cell may look like: =F129&G129

Cells F129 contains Mary and G129 contains Brown

Another cell, say, F132 contains the concatenation formula: =F129&G129

F132 will display: MaryBrown


The simple definition was to add two or more cells together that contained text. To add to the definition, you can add text cells and/or text strings together.

The next logical question in the MaryBrown case is that you probably want a space to separate the first and last name. In English you are saying add F129 to a specific text character not found in the cells, and then add G129 to that. The specific character just happens to be a space. So here it is:

The new concatenated formula: =F129&" "&G129

The concatenated cell with the space: Mary Brown

Now, let's add the prefix "Ms" in front of Mary's name. Since "Ms" is not in cell, we'll attach the text string in front of the concatenated name.

The completed concatenated formula: ="Ms "&F129&" "&G129

The cell would now display Ms Mary Brown

Note that when concatenating "Ms" that the formula still starts with a equal (=) sign, the text is surrounded by double quotes and the space needed to separate Ms from Mary was included inside the double quotes.

The Real Power

The are several uses for concatenating cells. One may be that you have an Excel database full of billing information. And you want to generate automated invoices from this database. For each record you select in the database, the invoice is ready to print automatically. Simply select the record and print, no further action needed. 

To set this up is beyond the scope of this article and requires the knowledge of other Excel features. However, it very doable and once it is set-up is a very powerful time saving feature.

Many uses of concatentation, such as the invoicing example, although powerful, may be too complicated for the time savings or the solution may be performed more efficently in other software. Advanced users can set this up fairly quickly, but less experience users should seek the assistance of an Excel user with the necessary skills and experience.

E-mail us for more information on advanced concatenation solutions.


 January 2, 1998