Concatenate

Ready to get super excited?! Here is a simple spreadsheet technique that is wicked useful!! Like me, you may want to name your next child “concatenate.”

Concatenate smashes stuff together.

Replace the word concatenate with the word “AND.” I want to smash this And this AND this….

Anywhere you say the word “AND” you put the ampersand symbol (&)

Concatenate Two Cells

Cell referencing means that instead of typing the words in the cell you put the cell address. This allows you to easily make changes. Instead of changing the formula, you just replace the cell contents.

Let’s pretend you have a first name in cell A1 and a last name in cell B1.

You want to smash these together. For example: Alice Keeler becomes AliceKeeler

Thus I want Alice “AND” Keeler. But instead of writing Alice, I put A1. And instead of writing Keeler, I put B1.

All formulas start with an equals sign.

In any blank cell you want type:
=A1&B1

This will smash (concatenate) the two cells together.
alice keeler alicekeeler concatenate

Spacebar

Perhaps you don’t want the first and last name smashed together. However, you do want them in the same cell.

You want the first name “AND” a space “AND” the last name.

All text strings need to be in quotation marks.

=A1&” “&B1

Last Comma First

If you want last name comma first name what you want is: last name “AND” comma spacebar “AND” first name.

=B1&”, “&A1

Don’t forget the quotation marks around your text string.

Concatenate with Text

You can smash (concatenate) text together when values in a cell. Let’s say you ask a student to write their name in cell A1.

You may want to write the student a message like this: Welcome Alice, you will want to look at the tabs along the bottom to work your way through this math problem. Alice, this will help you to persevere in problem-solving.

What you might want is: text “AND” name “AND” text “AND” name “AND” text.

=”Welcome “&A1&”, you will want to look at the tabs along the bottom to work your way through this math problem. “&A1&”, this will help you to persevere in problem-solving.”
“&A1&”

Numbering Things

I will be darned if I will type out a series of numbers. Spreadsheets will do this for me.

In a column on the spreadsheet type a 1 in a cell and a 2 in the cell below it. Highlight the two cells. Notice a small blue square appears in the bottom right-hand corner. Click and hold down on this blue square and drag DOWN. This will fill down and continue the pattern.
Fill Down

In the column next to the series of numbers, I will write the text I want to concatenate with the numbers.
Adjacent Text

For this example I want: the word group “AND” a spacebar “AND” the group number “AND” a spacebar “AND” the word leader

=C3&” “&B3&” “&D3

After pressing enter, click back on the cell with the formula. Click and hold down on the blue square in the bottom right-hand corner of the cell. Pull down to copy the formula and create a list.
concatenate with text

Digiprove sealCopyright secured by Digiprove © 2015