We know that your schedule or list data might not fit neatly into our Builder templates at first. With concatenation, you can more quickly and effectively manipulate your data so Builder knows how to read it.
Before diving in, please download and open the Concatenation Practice.xls spreadsheet attached at the bottom of the walkthrough so you can follow along.
Watch this video and/or follow the steps written below.
What Is Concatenation?
Concatenation is a jargony way of saying combination. In spreadsheets, you may have content in multiple cells that need to be combined - or concatenated - into one cell.
For example, you may have a spreadsheet of speakers where the First Name and the Last Name are in two separate cells. However, for Builder to read your imported template properly, we will need to put the speaker's full name in one cell.
Use concatenation to efficiently make this change across multiple rows of data at once.
How Do I Concatenate Data?
When you open this document, see the attachment at the bottom of this article, you will see a list of First Names and Last Names.
In a Custom List template, however, the "Name" is just one cell. We need to combine - or concatenate - the data in our spreadsheet so it will fit in Builder's template.
1. Click on the cell C1 (to the right of "Last Name"), and type in "Full Name".
2. Then click on cell C2 (just below your "Full Name" column header).
3. With the cell C2 selected, click in the formula (fx) bar. Type an equals sign "=" .
4. Now let's click on the cell A2.
5. Then type an ampersand (the "and" symbol that looks like &).
6. Then click on the cell B2. Your formula should read (=A2&B2).
7. Hit "Enter" on your keyboard to apply this concatenation formula.
You should see a Full Name in cell C2 that reads "AndrewKaufman".
Congratulations! You have successfully concatenated this data.
We need to put a space between the First Name and the Last Name. How do I do that?
Let's modify our formula just a little bit to add a space between the concatenated pieces of information.
1. Click on cell C2 (just below your "Full Name" column header) and delete the formula.
2. With the cell C2 selected, click in the formula (fx) bar. Type an equals sign "=" to start a new formula.
3. Now let's click on the cell A2.
4. Then type an ampersand (the "and" symbol that looks like &).
5. Then type quotation marks, space, quotation marks: " " . This tells your formula to include a space between the cells' data.
6. Now please type another ampersand ("and" symbol that looks like &).
7. Then click on the cell B2. Your formula should read: (=A2&" "&B2).
8. Hit "Enter" on your keyboard to apply this concatenation formula.
That looks much better. If you see "Andrew Kaufman" now, you're on a roll.
Do I have to type a formula for every single row of data in my spreadsheet?
Nope. Let's apply this formula to all of the rows so we don't have to type it again. This will be a huge time-saver.
Click on C2 (where your formula lives) and copy it. Select all the rows in that column below and then paste the formula.
You have successfully used concatenation to manipulate the data in your spreadsheet. Next, we need to put your information in a Builder template.
In this example, we set up a speakers list. Let's get a List Template and put these Full Names in it.
The concatenated data is full of formulas with equals signs and ampersands in it. We need to copy and paste a special way so the data comes over as text.
As shown below, you will need to select the Full Names from our worksheet and copy them.
Then open a Builder List Template.
Under the Names column, right-click and select Paste Special (or go to Edit >> Paste Special).
Select VALUES and click paste.
Finish filling out the Builder template, and then import your data.
- Looking for Schedule Template Import help? Click here
- Click here for Custom List Template Import instructions