Exceljet

Quick, clean, and to the point

Build friendly messages with concatenation

by Dave Bruns | April 6, 2017

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:

=B4&" apples"

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.

This video is part of our online video training.

Concatenation with number formatting

Once you get comfortable with concatenation, you'll start to notice many opportunities to concatenate values into more meaningful messages. Then, one of the first problems you'll likely run into is losing the formatting of numeric values you include in a message.

For example, let's say you have a due date in cell C4, and you want to display a message like "The project is due August 15, 2017".

So, you start off with this formula:

="The project is due on "&C2

However, when you hit enter you see: The project is due on 42962

Concatenation with unformatted date

It's kind of cool to see the underlying value, but most people don't know that August 15, 2017 is the 42962-th day in Excel's date numbering system, so not especially useful :)

To fix this problem, use the TEXT function to apply the formatting of your choice:

Concatenation with formatted date

The improved version uses this formula:

="The project is due on "&TEXT(C2,"ddd, mmm d, yyyy")

The TEXT function is a handy function you can use whenever you want to apply formatting to a numeric value and and up with text. You can use it for all number formatting, including percentage, currency, dates, times, and custom formats.

The video below shows how to use the TEXT function to increment a padded number (i.e. 0001, 00123, etc.)

This video is part of our online video training.

Clarify assumptions with concatenation

Another cool use of concatenation is to make assumptions clear in a model that requires specific user inputs or variables.

This video shows how concatenation can be used to "expose" several assumptions directly on the worksheet by concatenating variable inputs directly to calculation labels.

This video is part of our online video training.

Excel concatenation functions

Excel contains three functions you can also use for concatenation: the CONCATENATE function, the CONCAT function, and the TEXTJOIN function. CONCAT and TEXTJOIN are new in Excel 2016.

I'm not a fan of CONCATENATE, since it doesn't do anything you can't do with the regular old ampersand (&), which is much shorter, and more flexible to boot.

But CONCAT will let you join ranges, which is a new feature, and TEXTJOIN goes one step further and lets you join ranges with a delimiter of your choice. They are worth a look if you are using a newer version of Excel. This article discusses both functions in more detail: CONCAT and TEXTJOIN.

You are fabulous!!! It worked perfectly! Thanks a million :) :) - Gloria
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course