Build friendly messages with concatenation – Best article

One of the many cool things about Excel is that you can use formulas to display useful, dynamic messages directly on the worksheet. Dynamic messages give your spreadsheets a nice polish. Because they respond instantly to user input, the effect is friendly and professional:

Example of concatenation to show dynamic message

A key tool in building friendly messages is concatenation. Concatenation sounds complicated, but it’s a really simple and useful formula technique you can learn in a few minutes. You’ll find many of opportunities to use concatenation in your spreadsheets.

Caution: When people see messages like this this in a worksheet you built, they’ll assume you’re some kind of Excel genius, so be warned 🙂

A basic example

You can assemble messages with nothing more than a simple formula and a technique called “concatenation”. Don’t be alarmed by this fancy-sounding word. Concatenation simply means “join together”. In Excel this generally means joining text with a value from a cell, or with the result of a function.

For example, with the number 10 in cell B4, you can write a formula like this:

Which displays this message: 10 apples

Basic concatenation example

Note: text is fully enclosed in double quotes, and must include required spaces.

Here, the ampersand character (&) is used to join a text string with the value in cell A1. The ampersand is the “concatenation operator” in Excel, just like the asterisk (*) is the multiplication operator, the plus symbol (+) is the addition operator, and so on.

If a user updates cell B4 to contain 25, the message updates instantly:

Concatenation formula updates automatically

Embedding a value in the middle

You don’t have to concatenate only at the beginning or end of a text string, you can use multiple ampersands to embed values anywhere you like in a string.Taking the example above another step, you can use two ampersands to create a full sentence with a value in the middle:

="There are "&B4&" apples."

Which returns: There are 25 apples.

Concatenation to embed number between two text strings

Again note: all text must be enclosed in double quotes. If you forget to do this, Excel won’t let you enter the formula.

Concatenation with other functions

Once you get the basic idea of concatenation, you’ll quickly see how you can use the results of other formulas or functions in your messages.

For example, perhaps you maintain data in a filtered table. You often use one or more filters to narrow down data in the table, and you’d like to know how many records your viewing at any given time, and how many records are in the table total.

Building on the examples above, you can use concatenation, together with the row and subtotal functions to build a message like this: “Displaying X of Y records”

Where X is the total record count, and Y is the number of records currently visible, as in the screenshot below:

Using concatenation to show number of visible items in filtered table

The formula used is:

="Showing "&SUBTOTAL(103,Table1[Issue])&" of "&ROWS(Table1)&" issues"

Watch a video to see how to build out this formula step-by-step.