|Combining two or more text strings into one string||Back to List of Topics|
Understanding concatenated cell formulasDon'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
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 PowerThe 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|