This tutorial is compiled using Excel 2007 but should work on all versions of Microsoft Excel.
Often when using Excel you want to keep values in separate columns but also present them together. For example, when compiling a list of names you would want:
- First Name
- Full Name
You may also then want to add in numerical data as well. Let’s say this is for a customer list you may want customer ID and date contacted. Now your columns look like this:
- First Name
- Full Name
- Customer ID
- Date Contacted
- All Details
Immediately you realise that all the data in the ‘Full Name’ and ‘All Details’ columns is already entered. Instead of typing everything out twice – which often results in mistakes – you can use an Excel function to join multiple cells together. Here is how to join cells containing text and numbers:
Joining cells containing text in MS Excel 2007
To join two cells containing text simply use the function =A2&B2 where A2 and B2 are the cells you want to join.
As you can see, there is no space between the words, which isn’t ideal. So, we should add one in. Use the formula =A2&” “&B2 to add spaces to joined cells. Excel will add anything that appears between the “ ” but we will come to that later. Here is how the data looks now:
Remember that you must add & for everything you want to join. To complete the table simply highlight your completed cell and drag down from the bottom right corner.
Next we are going to create a column that combines text and numbers.
Joining cells containing text and cells containing numbers
This is the same formula as before. Join the cells using the ‘&’ function. When you do this Excel converts numbers into text and so can be useful for creating sentences using different forms of data.
In our example we’ve gone for a simple combination of cells. As you can see the formula only looks at the text in a cell and not it’s formula so you can join cells together with multiple formulas. Here’s an example:
Joining cells containing dates
The formulas above work for most standard data. However, they aren’t so good for dates. This is because Excel recognises a date as how many days since January 1, 1990. So the formula above ends up looking like this:
That’s not ideal for presenting data – nobody wants to work out what the date is 40,886 days after January 1, 1990!
To solve this we must tell Excel to format the date as text and also specify the format of the date. There are a number of ways to do this but here’s one example:
TEXT(E2,“ dddd, mmmm d, yyyy”) tells Excel to present the date in cell E2 as text in the order ‘day of the week, month day, year’. Here’s the formula in practice:
Our last tip for joining cells is to remember that if you delete the source data then the formula won’t work! If C3 is joining C1 and C2 then there must be data in cells C1&2, otherwise you receive the dreaded #REF.
If you really want to delete the source data but keep your joined cells then don’t fret, you can convert your formula to a value!
Converting Formulas to Values
Unfortunately there is no easy way to do this in Excel – i.e. a ‘convert to value’ button so you have to do it by copying and pasting.
Firstly, highlight and copy the cells you want to convert.
Then, with the same cells highlight, select ‘paste values’ from the paste drop down menu:
This pastes the same data into your highlighted cells but as values, not formulas. This allows you to then delete the source cells without losing any formulated data. As you can see below, the information in the cell is now a value:
Have fun joining cells!
For more Excel tutorials please browse through the site or read the highly popular posts