Nested Excel CONCAT Function-PART III

Excel CONCAT Function

The Excel CONCAT Function, which replaced CONCATENATE function and available from 2019, combines text from multiple ranges and/or strings. However, this function does not provide delimiter or ignore empty arguments. Unlike CONATENATE function, CONCAT function can join range of cells or strings.

List of Examples:

---

1. Join first, last, and middle names with some middle names missing.
2. Nested CONCAT Function with PROPER Function.
3. Nested CONCAT Function with UPPER/LOWER Function
4. Nested IF with CONCAT Function.

---

Syntax

CONCAT(text1, [text2],…)

where,

text: is the text or the reference to the cells with text.

Note: Maximum 253 text arguments are allowed in CONCAT function. If the resulting string exceeds 32, 767 characters (cell limit), CONCAT function returns #VALUE! error.

Example 1: Join first, last, and middle names when some middle names missing:

In our example below, we have first, last, and middle names. Some middle names are missing. We need to join all three parts of the names. To do so, we can combine IF, COUNTA, and CONCAT functions, and the formula is =IF(COUNTA(E6)=1, CONCAT(C6, ” “, E6, ” “, D6), CONCAT(C6, ” “, D6)), which returns all the parts of the names as we want.

Example 2: Nested CONCAT Function with PROPER Function:

To nest CONCAT with PROPER, the formula is =PROPER(IF(COUNTA(E6)=1, CONCAT(C6, ” “, E6, “.”, ” “, D6), CONCAT(C6, ” “, D6))), which joins names with proper letter cases.

Example 3: Nested CONCAT with UPPER:

To nest CONCAT with UPPER, the formula is =UPPER(CONCAT(C6, ” “, E6, “.”, ” “, D6)), which joins the three parts of names and generates the UPPER case of the letters. In the similar manner, we can nest CONCAT with LOWER.

Example 4: IF function nested in CONCAT:

To nest IF function within CONCAT, the formula is =CONCAT(IF(D6:D9=”RM”, C6:C9, “”)), which joins texts based on IF conditions.

Related posts:

MS Office post on CONCAT

CONCAT PART I

CONCAT PART II

REPLACE Function

SUBSTITUTE Function

ARRAYTOTEXT Function