Concatenation is the process of stringing together different pieces of information to form a new piece of data. For example, if we have columns containing people’s title and first name and another containing their last name, we could use concatenation to create their full name. All we need to do is to string together the title, first name and last name.
Excel offers two concatenation techniques. The first is to use ‘&’ (the ampersand) which is the concatenation operator: the second is to use the CONCATENATE function. To use the ampersand in our Full Name example, we would first enter an “=” sign to indicate to Excel that we want to create a function. Secondly, we would click on the cell containing the title to enable 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.
Let’s now look at doing the same thing using the CONCATENATE function. When using the Insert Function command, the CONCATENATE function is found in the Text category. It allows you to string together up to 255 pieces of information. Let’s say, for example, 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 method is better; there is not much to choose between them. Simply use the one which seems simpler or more logical to you. One argument for using the first method is that the use of a concatenation operator is found in most scripting and programming languages. Some environments use an ampersand; others use a plus sign.
If you would like to learn more about Excel VBA training courses, visit Macresource Computer Training, a UK IT training company offering Excel VBA training courses at their central London training centre.
July 19, 2010 by Michael Charles
Related Articles
Comments
2 Responses to “The Different Ways Of Concatenating Data In Excel 2007”Speak Your Mind
Tell us what you're thinking...and oh, if you want a pic to show with your comment, go get a gravatar!
I’ve given up a combined 92 points in my first two online Madden 11 games. Ouch. I’ve scored a combined 74 points in those two games.
Nice site to visit! I admire your writing and the way you explain things. Some of the comments on here too are insightful. I appreciate you. keep it up!