Combining Cells

Combining Cells

There may be times when you need to concatenate , or combine, text and calculated numbers in a single cell. You may also need to combine the contents of multiple text cells or multiple calculated amount cells into a single cell .

Figure 20-24 shows two concatenation examples.

Figure 20-24. Concatenation examples

There are two ways to concatenate cells: a simple way that lets you combine text with a single cell reference or formula (used in the first example in Figure 20-24), and a more advanced way that lets you combine multiple text strings and cell references (used in the second example in Figure 20-24).

Simple Concatenation

When you want to combine text with a single cell reference or a formula (that can include multiple cell references), use the following syntax in a cell:

 ="The text you want to type "&C5 

or

 ="The text you want to type "&sum(C2:C4) 

After you type the ampersand (&) in the formula, you can click cells in the spreadsheet rather than typing them manually to insert them in the formula.

Notice this is written as a formula, which begins with the equals sign (=). Notice the space between the last letter of text and the quotation marks. You need to add that space if you want a space between the text and the amount.

Here is how the first example in Figure 20-24 is written:

 ="The Q1 sales total is "&B10 

Advanced Concatenation

When you want to combine multiple amounts and text strings in a cell, use the following syntax in a cell. This involves using Calc's CONCATENATE function.

 =CONCATENATE(cell/text/formula;cell/text/formula;...) 

In this syntax example, cell/text/formula means you can use a cell reference, text, or a formula. Each item is separated by a semicolon (;). You can use as many items as you want.

Here's how the second example in Figure 20-24 is written:

 =CONCATENATE("The total of";D4;" and ";E4;" is ";SUM(D6:E6)) 

Notice the spaces in the " and " and " is " items. These are necessary to separate the text from the amounts.

This syntax of using a semicolon (;) to separate items is an important difference between Excel and Calc. In Excel, you separate items with a comma (,).

In this example, there are five semicolons that separate six items. You could instead write the formula differently to combine text and numbers, reducing the number of items you need to separate from six to three:

 =CONCATENATE("The total of "&D4;" and "&E4;" is "&SUM(D6:E6)) 

This technique is accomplished by putting an ampersand in front of a cell reference or formula when it's directly combined with text, as illustrated in the previous Simple Concatenation procedure.