Tips for Excel, Word, PowerPoint and Other Applications

Concatenating Text in Excel

Why It Matters To You

con·cat·e·nate tr.v. To connect or link in a series or chain.

There are times when you might want to chain cell values together, combine cell values with text, or chain cell values together and add text. For example, you could add a dynamic variable to the sentence, "Savings for 1Q05 are $...." If nothing else, it's a really cool word.

How To ...

There are two ways this can be done: CONCATENATE and &. I typically use these functions in two ways: combining variables and text to make dynamic notations and combining column data (CONCATENATE) for improved data modeling flexibility (e.g., month and location) to model monthly spend by office. For this excercise, download the example file, concatenate.xls.

CONCATENATE

Concatenate simply joins text strings, numbers, or cell references together into a single cell output

For example, below I have 4 cell values and a concatenate function to chain them all together. Play around with the input values and see what you get.

Function: =CONCATENATE(C4,C5,C6,C7)
Result (raw): 12BuckleMy Shoe
Result (with text spacers): 1 2 buckle my shoe

Notice that 1) any numbers loose their formatting and you have to add the spaces in any text, 2) you have chain discrete items, not formulas. Any text strings entered as a CONCATENATE argument has to be encased in ".

& (aka the Ampersand)

One tip, 2 cool new words

The ampersand (&) is another way of combining text and variables. The general rules are 1) text has to be encased in " and 2) text and variables are separated by a & symbol.

For example, I'll use the first 2 inputs above:

Function: ="I bought "&$C$5&" pears for $"&$C$4&" a piece"
Result: I bought 2 pears for $1 a piece

Just like CONCATENATE, 1) any numbers loose their formatting, 2) you have to add the spaces in any text, and 3) all text strings entered as a CONCATENATE argument has to be encased in ".

You can also add formulas to the & statement.

Function: ="I bought "&$C$5&" pears for $"&$C$4&" a piece. A total cost of $"&$C$4*C5&" dollars"
Result: I bought 2 pears for $1 a piece. A total cost of $2 dollars

Another Example

Desired Text: Revenue was up [Revenue Change] in [Current Month]
Formula: ="Revenue was up $"&C19&" in "&C18
Result: Revenue was up $1000 in August

Desired Text: Revenue was [UP/DOWN] [Revenue Change] in [Current Month]
Formula: ="Revenue was "&C26&" $"&C25&" in "&C24
Result: Revenue was DOWN $-200 in August

Conditional formatting, not covered here, used to change text color based on Flat, Down, or Up.

Notes

Last updated	9/8/07
Application Version	Excel 2003
Author	Michael Kan
Pre-requisites	None
Related Tips	None