Pro Tip: Use Concatenation to Set Up Import Template Spreadsheets

Follow

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. 

In this article, we hope to answer the following questions:


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. 

Screen_Shot_2017-06-07_at_6.37.46_PM.png

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?

Please download and open the "Concatenation Practice.xls" spreadsheet at the bottom of this walkthrough.

When you open this document, you will see a list of First Names and Last Names.

Screen_Shot_2017-06-07_at_6.41.46_PM.png

 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 "=" . 

Screen_Shot_2017-06-07_at_6.45.26_PM.png

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.

Screen_Shot_2017-06-07_at_6.48.26_PM.png

You should see a Full Name in cell C2 that reads "AndrewKaufman".

Congratulations! You have successfully concatenated this data! 

 

Wait! 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.

Screen_Shot_2017-06-07_at_6.55.56_PM.png

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. 

copy_and_paste_formula.gif

  

OK! Now what?

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.

Note: 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. 

 

paste_special.gif

Nice!

Finish filling out the Builder template, and then import 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.  

 


Need more help? Feel free to reach out to us at Support@guidebook.com.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk