Microsoft Excel 2003: How To Join Multiple Cells Together Using Text Concatenation

By Author: Chris Le Roy
Total Articles: 95
Comment this article

One of the issues you maybe faced in Microsoft Excel 2003 is having to combine various cells together into one single cell. For example, you may have three columns named, Title, First name and Last name and you maybe asked to combine those three values together. To achieve this capability we use the Text Concatenation Function. This article will look at the process of combining these cells together.

To begin, open Microsoft Excel 2003 and create a new worksheet. We now want to create a very simple worksheet that will allow us to join multiple cells together. Simply create the Microsoft Excel 2003 worksheet as shown below.

Title First Name Last name Date of Birth
Mr Joe Smith 19/10/2003
Miss Martha Henry 20/02/1945
Ms Kerry Walken 15/02/1952

You should start building the worksheet by clicking in cell A1 and the value of Title should be in this cell. The next value Mr should be in cell A2 and so forth. Once you have completed creating this ...
... worksheet then go to cell E1 and type in the word Statement, then select E2.

What we are now going to do is to combine cell A2, B2, C2, D2 using the text concatenation function which is the ampersand & symbol. However, there is a catch you cannot automatically just combine Dates and Numbers with Text cells. To achieve this capability we need to use the TEXT function.

Let us focus first on adding the Title, First name and Last Name together. First select cell E2 and type the following -

= A2 & " " & B2 & " " & C2

You will notice in the formula above that there is a space between the double quotes. This is done to ensure that the text in the fields do not combine together, otherwise you would have a result that would look like -

MrJoeSmith

Now that we have multiple text cells together it is now time to look at the process of adding non-cell text and also a date to your text. To add a date to your text you must first convert it to text. You achieve this using the TEXT function. The TEXT function also allows you to format the date in a specific format. For example if I wanted a date in the following format -

09/09/2008

I would enter the Text function as -

TEXT( Cell Address, "dd/mm/yyyy")

Now that we have that covered it is time to incorporate the Date into the cell. Simply type the formula as follows -

= A2 & " " & B2 & " " & C2 & " was born on " & Text(D2,"dddd, dd mmmm yyyy")

The cell should now look like -
Mr Joe Smith was born on Sunday, 19 October 2003

The cool part about Text concatenation is that you can use it with Excel Database lists and combine multiple cells together and then use the data in the list with programs like Microsoft Word to do a mail merge. It is much easier in Microsoft Word to insert single fields then to bring multiple fields together.