CONCATENATE function

This topic describes the formula syntax and usage of the CONCATENATE function in the Data Quality Execute Formula transform.

Description

Joins several text items into one text item.

Syntax

CONCATENATE(text1,[text2],...)

The CONCATENATE function syntax has the following arguments:

text1 Required. A field identifier, literal string number, or formula. Numeric values are converted to text.
text2,... Optional. A field identifier, literal string number, or formula.

Example

Use the following formula to assemble the full name from its parts:

CONCATENATE([First]," ",[Middle]," ",[Last1]," ",[Last2])

Table 1. Data
First	Middle	Last1	Last2	Result
`Victoria`	`Luisa`	`Pineda`	`Romero`	`Victoria Luisa Pineda Romero`
`Marco`	`Antonio`	`Manzanares`	`Solis`	`Marco Antonio Manzanares Solis`
`Maria`	`Cruz`	`Seren`	`Vasquez`	`Maria Cruz Seren Vasquez`

Typically, the middle name is optional and the second last name is either omitted or not used in a region, so we can use a formula to omit the space before missing values:

=CONCATENATE([First],IF(ISBLANK([Middle]),," "),[Middle]," ",[Last1],IF(ISBLANK([Last2]),," "),[Last2])

Table 2. Data
First	Middle	Last1	Last2	Result
`Victoria`	`Luisa`	`Pineda`		`Victoria Luisa Pineda`
`Marco`	`Antonio`	`Manzanares`	`Solis`	`Marco Antonio Manzanares Solis`
`Maria`		`Seren`	`Vasquez`	`Maria Seren Vasquez`