Tell us what you think

We want to create the best possible experience for our learners. Your feedback helps us to improve the experience for everyone.

Concatenating Character Strings

Objective

After completing this lesson, you will be able to Concatenate character strings.

Concatenated Strings with a Date

When concatenating a string with a date, the date is converted to a string type.

Applications of Concatenation

Combining text strings with data can help to clarify what is presented in your report. The following list shows two common applications of concatenation:

When you want the report title to include prompt responses to clarify the scope of the data presented.
For example, in a sales revenue report for the year 2019, the report title is Sales Revenue Report for 2019. To provide extra clarity, concatenate the LastExecutionDate with the title so that the title becomes Sales Revenue Report for 2019 as of 10/31/2019.
When you want to provide information in a sentence.
For example, you can add a descriptive sentence, such as Colorado's best sales year was in 2019 at $843,584 to a report.

To explore concatenating different data types, we will combine a string value and the Last Refresh Date free-standing cell formula.

The value displayed in the Last Refresh Date free-standing cell is the date and time when the document was last refreshed; the date and time is displayed in the format 4/10/19 3:15 PM.

The syntax for the formula is as follows:

Code Snippet

1
date LastExecutionDate(string data_provider)

This formula returns the date on which a data provider, or query, was last refreshed. Enclose the name of the data provider in brackets. If your report only has one data provider, you can omit the data_provider parameter.

You can insert the Last Refresh Date free-standing cell into a report and view the formula in the Formula Editor.

The formula displays as follows:

Code Snippet

1
=LastExecutionDate()

To insert a descriptive string before the date, modify the formula as follows:

Code Snippet

1
="Last Execution Date: " + LastExecutionDate()

This formula concatenates the Last Execution Date character string with the LastExecutionDate() function.

The Last Refresh Date cell now displays as follows: Last Execution Date: 4/10/19.

Formatted Dates and Concatenated Strings

As well as being able to concatenate dates with strings, you can also display a date using a character string.

If the formula only contains the LastExecutionDate function, then you can choose Format Display in the context menu of the cell and then choose from the Date/Time area of the Format Display dialog box and the desired date format.

However, now that the formula includes a character string, you cannot use the standard formatting method. To format a date that is concatenated with another string and display it as a string, use the FormatDate() function.

The syntax for the FormatDate() function is as follows:

Code Snippet

1
string FormatDate(date ; format_string)

An example of the FormatDate() function is as follows:

Code Snippet

1
=FormatDate([Start Date] ; "dd/MM/yyyy")

An example to modify the Last Refresh Date formula by using the FormatDate() function is as follows:

Code Snippet

1
="Last Execution Date: " + FormatDate(LastExecutionDate() ; "Mmmm dd, yyyy")

To convert a number to a string, use the FormatNumber() function.

The syntax for the FormatNumber() function is as follows:

Code Snippet

1
 string FormatNumber(number ; format_string)

Apply String Manipulation Formulas

ExerciseStart Exercise

Next lesson