Concatenation is the simply the process of stringing together different pieces of information to form a new piece of data. For example, if say we have columns containing people's title and first name and another containing their last name, we might use concatenation to create their full name. All we need to do is to string together the title, first name and last name, separated by spaces.
Excel offers two methods of concatenating data. The first is the use of the ampersand ('&') which is the concatenation operator: the second is the use of the CONCATENATE function. To use the ampersand in our 'Title, First Name, Last Name' example, we would first type in "=" to indicate that we want to create a function. Secondly, we would click on the cell that contains the title to have Excel add a reference to that cell to our formula.
Next, we need to add a space to separate the title from the first name. To do this, we type the concatenation operator followed by a space and since a space is a character it has to be enclosed in quotation marks. Thus, we would type '& " " &'. After that, we click on the cell that contains the first name, then type '& " " &' once more to add a space separating the first name from the last name.
To finish, we click the cell that contains the last name and, to confirm the formula, either press the Enter key or click on the Enter button on the left of the formula bar.
Now let's look at doing the same thing using the CONCATENATE function. When using the Insert Function command, this is found in the Text category and permits you to string together up to 255 pieces of information. For example, let's say that our title was in cell C2, our first name in D2 and our last name in E2; our formula would be '=CONCATENATE(C2," ",D2," ",E2)'.
As to which is better; there is not much to choose between them. Simply use the one which you feel makes more sense to you. One argument for using the first method is that the use of the concatenation operator is found in most scripting and programming languages. Some environments use the ampersand; others use the plus sign.
Excel offers two methods of concatenating data. The first is the use of the ampersand ('&') which is the concatenation operator: the second is the use of the CONCATENATE function. To use the ampersand in our 'Title, First Name, Last Name' example, we would first type in "=" to indicate that we want to create a function. Secondly, we would click on the cell that contains the title to have Excel add a reference to that cell to our formula.
Next, we need to add a space to separate the title from the first name. To do this, we type the concatenation operator followed by a space and since a space is a character it has to be enclosed in quotation marks. Thus, we would type '& " " &'. After that, we click on the cell that contains the first name, then type '& " " &' once more to add a space separating the first name from the last name.
To finish, we click the cell that contains the last name and, to confirm the formula, either press the Enter key or click on the Enter button on the left of the formula bar.
Now let's look at doing the same thing using the CONCATENATE function. When using the Insert Function command, this is found in the Text category and permits you to string together up to 255 pieces of information. For example, let's say that our title was in cell C2, our first name in D2 and our last name in E2; our formula would be '=CONCATENATE(C2," ",D2," ",E2)'.
As to which is better; there is not much to choose between them. Simply use the one which you feel makes more sense to you. One argument for using the first method is that the use of the concatenation operator is found in most scripting and programming languages. Some environments use the ampersand; others use the plus sign.
About the Author:
The author is a trainer and developer with Macresource Computer Solutions, a UK IT training company offering Microsoft Excel 2007 Classes in London and throughout the UK.
