Specific functions: Concatenate

Ah, concatenate. How I love you! I use you for proofreading. I use you to generate statistics. I use you to write code. I use you to enable easy sorting. And, of course, I use you for formatting text.

As I mentioned before, concatenating is putting together strings of text, rather like stitching together a baby’s quilt with letters and numbers on all the squares. There’s no practical limit to how big it can get although, much as with a quilt, if you’re aiming for a mile-long result it might be easier to stitch together 100 50-foot sections than it would be to stitch together 5000 1-foot sections. So sometimes it’s better to break up a single large or very complicated concatenate statement into multiple smaller statements, and then concatenate their results.

General Instructions
As with any function, you start by selecting the cell you want the function to be in, and then enter it either by typing or by using the menus to retrieve it. The syntax, limitations, and work-arounds for problematic characters vary somewhat by program.

What do I mean by work-arounds for problematic characters? Well, for example, most spreadsheet programs use ” to indicate that something is text . But the ” disappears in the process. So how do you get a ” to appear in the result? Or how about the fact that most spreadsheet programs treat the enter key as an instruction to move to another cell? In that case, how does one insert a line into the concatenated text?

No matter what spreadsheet program you’re using, if I say text1 I mean you can enter text inside quotation marks, or a reference to or name of a cell or range of cells. As a general rule, if you use a range of cells then no more than one cell of that range can be on the same row or column as the cell the function is in. This is because, for example, if you were in cell b2 and specified all of row 1, it could assume you wanted cell b1. Likewise, if you were in cell b2 and specified all of column C, it could assume you wanted cell c2. However, if you specified both column C and column D in the same range, it wouldn’t know if you wanted c2 or d2.

In Excel 97:

  • Syntax: =concatenate(text1,text2,etc.)
  • Category: Text
  • Limitations: At most 30 parts. (text1…text30).
  • Work-arounds:
    • Quotation marks:To put in a single quotation mark, enter 4 quotation marks. =concatenate(“”””,”Example”,””””) would yield “Example”.
    • New lines:Go to Word. Hit Enter. Highlight both lines, and copy them. Go back to Excel, and then in your concatenate function, inside quotation marks, paste what you copied in Word.

In Excel 2007:

  • Syntax: =concatenate(text1,text2,etc.)
  • Category: Text
  • Limitations:
  • Work-arounds:
    • Quotation marks: To put in a single quotation mark, enter 4 quotation marks. =concatenate(“”””,”Example”,””””) would yield “Example”.
    • New lines: Go to Word. Hit Enter. Highlight both lines, and copy them. Go back to Excel, and then in your concatenate function, inside quotation marks, paste what you copied in Word.

In Google Spreadsheet:

  • Syntax: =concatenate(text1,text2,etc.)
  • Category: Text
  • Limitations: At most 30 parts. (text1…text30). Also, if you try to concatenate a named column, it will concatenate everything in the column, not just the cell on the same row as the function.
  • Work-arounds:
    • Quotation marks: To put in a single quotation mark, enter 4 quotation marks. =concatenate(“”””,”Example”,””””) would yield “Example”.
    • New lines: None. You can put it in using copy/paste, but it will disappear.

In OpenOffice Calc:

  • Syntax: =concatenate(text1; text2; etc.)
  • Category: Text
  • Limitations: At most 30 parts. (text1…text30).
  • Work-arounds:
    • Quotation marks: To put in a single quotation mark, enter 4 quotation marks. =concatenate(“”””,”Example”,””””) would yield “Example”.
    • New lines: None. You can put it in using copy/paste, but it will cause an error.

In Microsoft Works Spreadsheet: Does not exist.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s