Pro Tip: Using Concatenation and HTML tags to format your spreadsheets

Follow

Thanks for checking out our Pro Tip series!  In this article we will show you how to use concatenation and html tags to help you manipulate your spreadsheet data quickly, efficiently, and with greater control over the format of your data.  

Required Materials:

1) You'll want to open up your favorite spreadsheet editor (we recommend LibreOffice, Apache OpenOffice, or Microsoft Excel)

2) Download the Concatenation Example attachment from the bottom of this support article.  

 

Table of Contents

1. What is concatenation, and why do I want to use it? (Plus, some examples) 

2. Including HTML in Spreadsheet Uploads

3. More Resources

 

1. What is Concatenation, and why do I want to use it?

Concatenation is a jargony word, but you can think of concatenation as a great 'combiner'.  In spreadsheets, we'll be using concatenation to merge the contents from multiple cells into one cell. For instance, imagine you have a list of people comprised of two columns -- column A holds first name info, and column B stores last name data.  This example is illustrated in the screenshot below, and it corresponds to the first sheet of the Concatenation Example attachment:

 

If you've taken a look at our custom list template, you may have noted that we only have a single column for name data.  The example above would not transfer smoothly into Guidebook's template...or, would it...

The solution is rather straightforward.  We'll concatenate (or combine) the first and last names together to create the full name. To perform this operation, click into the top formula bar and enter "=".  This informs your spreadsheet program that you are going to use a formula to define the contents of the cell. Then, select the first cell (A2), use the "&" symbol, and select the second cell (B2) -- hit enter when finished. You can copy and paste this formula to the other cells, or click the bottom right of the cell and drag.  The result should look something like this (with the formula as =A2&B2):

 

Our formula combines the text in each cell.  You'll note that our C column looks a bit strange -- we forgot to add in a space between the two words.  To get the extra space, we'll need to tweak our formula to include a space between the contents of column A and column B.  To do this, we'll use empty quotation marks separated by a space (" "), the formula as =A2&" "&B2.

 

 

Voile!  We have a single column that could fit our custom list template!  Note: Since our Column C is full of formula data (as opposed to text), we will need to copy and paste this data using paste special (choosing to paste only the values, not the formulas).  If we do not paste special, our cells will act funny when we delete or reshuffle some data.

 

Another Example (sheet 2 in the Concatenation Example attachment)

Let's walk through a more complex example.  In our Spreadsheet Template, we only offer one description field. Often there are additional pieces of information organizers want to include in custom list/schedule items. If you have three fields that you need to combine into one description field, concatenation is the perfect tool to get the job done. 

In our earlier example, we used ampersand notation ("&"), but there's another way to concatenate.  If you are concatenating more than 5 items, I suggest that you use the =concatenate() function instead of using ampersands ("&"). The concatenate function takes as many different inputs as you'd like, separating each entry with a comma, and returns all of the entries as a single unit.  Below, we are using the formula

=CONCATENATE(A$1,": ",A2,"<br><br>",B$1,": ",B2,"<br><br>",C$1,": ",C2)

to combine our data.  You can ignore the $ symbol for now (For more info, check out this note on stackoverflow), but let me break down this formula:

1. We are starting with the value in A$1 -- this is "ID".

2. We use a comma and then enter a colon followed by a space (": "). To concatenate plain text, include quotes around the text to be included.

3. We add in the value from cell A2.

4. We add in the HTML tag for line break <br> in quotes (because it's technically plain text). We'll discuss HTML lower in this article.

The rest of the formula uses the same parameters mentioned above.

 

 

When the formula has been fully typed out, we'll use the enter key to execute the formula.  We can then copy/paste the formula through the cells or click and drag down.  It's worth rementioning our note from the first example -- Since our Column D is full of formula data (as opposed to text), we will need to copy and paste this data using paste special (choosing to paste only the values, not the formulas).  If we do not paste special, our cells will act funny when we delete or reshuffle some data.

 

As you can see, concatenation is quite powerful; while it may appear tricky, we encourage you to play around with the examples in the attached spreadsheet.  Shoot us a message at support@guidebook.com if you have any questions.

 

HTML

The Guidebook Builder can actually read some HTML data when importing spreadsheets.  In the example above, you may have noticed use of the html tag <br>.  To use HTML in your Guidebook, you can add HTML tags to the spreadsheet you are importing; or, you can navigate in our online Content Management System (Guidebook Builder), find a session or list item, and use our HTML editor (see the screenshot below). Tags are only supported in the description fields of items and sessions; moreover, not all tags are supported by Guidebook, so be sure to check the list below for compatibility:

Allowed tags: a, b, br, i, ul, li, h1-h6, span & strong

 

Hyperlinking using HTML

If you want to label your URLs or import URLs that will send emails, you'll need to use <a href tags.  Generally, you'll use <a href="{url here}">Name of Link</a> (for example, <a href="http://guidebook.com">Guidebook Home Page</a>).  However, because quotation marks are needed for <a href, and concatenation requires quotations, these types of operations can be a little tricky.  Thankfully, you can use a few of the following tips to get these URLs formatted perfectly.

1) In an empty cell (for this example, we'll use cell L2), please type " (yes, literally quotation marks).

2) You'll want all of your URLs to be in a single column (let's assume they are in column M, and that the first URL appears in M2).

This example will also assume that all of your URLs will be labeled "Registration Link".

3) Now, we can concatenate.  =CONCATENATE("<a href=",$L$2,M2,$L$2,">Registration Link</a>").

You can copy and paste this formula all the way down your columns; because we use $ symbols, $L$2 will always reference the quotation mark.  You can use this same strategy to create email links (<a href="mailto:{emailaddress}">emailaddress</a>) 

 

More Resources

For more information on concatenation, I encourage you to check out Microsoft Excel's concatenation tutorial.  W3Schools has some great documentation on HTML.  You'll also find some great Guidebook tips on adding sessions and custom list data in the following articles:  

Adding Sessions

Adding Custom List Items

 

 

 

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

Comments

Powered by Zendesk