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.
What Is Concatenation?
Concatenation is a 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.
We can use concatenation to efficiently make this change across multiple rows of data at once.
How Do I Concatenate Data?
In general, to concatenate cells together, you'll use a formula that looks something like this “=A2&B2”. The ampersand tells Excel to join the contents of cells A2 and B2 into a single string of text. We'll work through an example below.
Before diving in, please download and open the Concatenation Practice.xls spreadsheet attached at the bottom of the walkthrough so you can follow along.
When you open the attachment at the bottom of this article, you will see a list of First Names and Last Names in columns A and B.
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 the “Full Name” column header.
3. With the cell C2 selected, click in the formula (fx) bar. Type an equal sign “=”.
4. Now click on the cell A2.
5. Type an ampersand (the “and” symbol that looks like &).
6. Then click on the cell B2. Your formula should read “=A2&B2”.
7. Click Enter or Return 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. However, since we'd like there to be a space between the first and last names, we need to modify the formula.
1. With the cell C2 selected, click in the formula (fx) bar after the first ampersand (&).
2. Type quotation marks, space, quotation marks, ampersand: " "&. This tells your formula to include a space between the cells' data. Your formula should read: “=A2&" "&B2”.
4.Click Enter or Return on your keyboard to apply this concatenation formula.
We'll apply this formula to all of the rows so we don't have to type it again which will be a huge time-saver.
Click on C2—the cell that says “Andrew Kaufman”— and copy it. Select all the rows below C2 in column C and 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, so you'll want to download a custom list template from Builder if you haven't already. We'll then copy the Full Names column into the Name column of the Builder template.
The concatenated data is full of formulas with equals signs and ampersands in it. Because of this, we need to paste a special way so the data comes over as text.
Open both the spreadsheet that has your concatenation formula in it, as well as your custom list template. Select all the Full Names in cells C2-C7 of your concatenation data.
Under the Names column in the custom list template, right-click and select Paste Special, or click Edit, and select Paste Special.
Select the Values radio button and click the OK button.
If you paste from your concatenation spreadsheet to your Builder template normally, you will get a “#REF!” error. This is because, rather than copying the full names, you are trying to copy the formula, and Excel does not know what cells you are referencing.
Finish filling out the Builder template, and then import your custom list data. If you are using concatenation in a schedule template, this support article has more information about importing schedule templates.
Advanced Concatenation
You can use concatenation for more than just combining the contents of two cells. In fact, you can combine as many cells as you would like.
For example, you may want to include addresses of Sponsors or Exhibitors in the description field. If your address information is across several columns, you can use concatenation to quickly bring all the address information together. After applying the formula, you can copy that data into your list template to import into Builder.
The “<br>” refers to an html tag. When you import this into Builder, Builder will read the “<br>” and enter a line break.
You can also include other html tags in your description of a list item or session. It can help to use concatenation if your data is similar and the same format can be applied. For example, if you would like to include the moderator's name in a session description, you may want the description to look something like this:
We'll include a few additional columns in our data that include html tags. After copying the finished formula into our Builder template using Paste special, when we import our spreadsheet into Builder, the descriptions will be formatted the way we'd like. Feel free to reach out to us at support@guidebook.com if you have any questions about formatting your data.
Additional Resources
For more information on concatenation, check out Microsoft Excel's concatenation tutorial. W3Schools has some great documentation on HTML as well.