Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Google Sheets: Concatenate … You’re Welcome

Google Sheets: Concatenate … You’re Welcome

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



3 thoughts on “Google Sheets: Concatenate … You’re Welcome

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

Join Alice Keeler, Thursday Mar 28th or register to gain access to the recording.
Create a free OTIS account.

Join Alice Keeler for this session for a way to create dynamic and interactive digital lessons. The Desmos platform is completely free and allows for any topic to be created or customized.

Exit this pop up by pressing escape or clicking anywhere off the pop up.