Use TEXT and & to combine text, numbers, and dates for dynamic titles and labels

Formatting

A great way to make your charts and dashboards more usable and include dynamic titles, legends, and text is with a combination of the TEXT and concatenation. Here are some examples,

One of our readers, Joe, added a dynamic title to the scrolling project timeline described in Creating Dynamic Excel Timelines that Scroll.  His cell formula was,

=”Timeframe: “&TEXT(H5,”mmm dd”)&” – “&TEXT(H16, “mmm dd”)

This uses the text, Timeframe, and concatenates it with the first and last dates used in the scrolling timeline. Pretty cool. Thank you Joe for sending in this idea!

Adding a dynamic title that concatenates text and date makes this a more usable scrolling timeline.

Adding a dynamic title that concatenates text and date makes this a more usable scrolling timeline.

Another example is a related post, Creating Dynamic Excel Chart Titles that Link to Worksheet Cells.

Dynamic Excel chart titles link to cells and show multiple text lines.

Dynamic Excel chart titles link to cells and show multiple text lines.

You can make almost any text dynamic so it changes as data and dates change. Here’s an example of creating a dynamic legend in the post Creating Dynamic Excel Chart Legends that Link to Worksheet Cells which produced this example,

Create dynamic legends that link to cells and show multiple lines.

Create dynamic legends that link to cells and show multiple lines.

It’s easy in Excel to combine text, numbers, or dates. Use the TEXT() function to convert numbers or dates into text, then use the concatenation symbol, &, to join the textual numbers or dates with real text. The numbers, dates, or text can be explicitly written in the function or referenced in a cell or formula.

For example, if C12 contains the text, “Today is”, and today is April 1, 2016 then

=$C$12&” “&TEXT(TODAY(),”ddd mmm d, yyyy”)

Returns to the cell containing the formula

Today is Wed Mar 4, 2015

 Use the concatenation character, &, to join text, numbers and dates. This enables you to easily put numbers and dates in titles, legends, labels, and numeric report layouts.

If you do a concatenation and you see your dates appear as a number like 42342, then you are seeing the serial date number Excel uses to identify dates. You need to enclose the date reference in the TEXT() function and specify a date format to make it appear correctly.

See these related links,

Creating Custom Date, Number, or Percentage Formats

Custom Format Layouts

Custom Numeric Formatting Symbols

Custom Date and Time Formatting Symbols

Hiding Numbers with a Custom Format

Hiding Zeros in Cells or Worksheets

Fitting Long Numbers into Narrow Cells

Share the power...