Merging Columns

As you know, you can include full sentences of information in your spreadsheet. This is a good way to include details about some students and not about others. For example, you may have the high school or parents' names of some of your students but not others. When this happens you'll want to include these details in the form of a full sentence inside your spreadsheet.

However, because the spreadsheet you received obviously didn't come with full sentences, you'll want to create them manually. Luckily, you can write a formula in Excel that will create a full sentence in a specific cell. This guide will walk you through that process.

For this example, we'll use a spreadsheet in which some students have parents names listed and some do not.

The first thing you'll want to do is create an open column in your spreadsheet. Also, I'd recommend sorting your spreadsheet so that all of the rows you're adding text for are at the top of your spreadsheet - This makes copying and pasting easier.

Next, we're going to start entering a formula into the open column. Formulas in Excel are easy to use once you get the hang of them. Every Excel formula begins with an = sign. When you want to bring in text from one of the cells in your spreadsheet you should refer to is the cell's alpha-numeric value A1, B2, and so on. A standard formula will look something like this: =A1&" "&B1&" "&C1&"

Here you'll notice that our cells numbers are surrounded by ampersands and then again by quotation marks. In this case, "&B1&" will tell Excel to use at the contents of cell B1 word for word. If we'd like to, we can add some additional text, spacing and punctuation to our formula to turn it into a complete sentence. Notice the formula in the image below:

In the example above we used the following formula: =B2&" is the "&H2&" of "&F2&" of "&G2&".

When we hit return this formula will become: Patrick is the daughter of Bernard Patrick of Lubbock.

We'll then immediately copy the contents of this cell and paste it into the cells where we'd like to add this formula.

As you can see, each row will become populated using the data from their corresponding columns. However, at this time the contents of these cells are formulas and NOT the actual results. In order to change the text to the actual values, we will want to copy the contents of our column and then right click and choose the Paste Special option.

Here we'll choose the Values option, which will paste the actual text into our column.

Now our spreadsheet will contain a full sentence of text that describes the students' parents.