Excel CONCAT Function (10 Examples)-PART II

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. CONCAT text from a column with space.
2. CONCAT function with Dates.
3. CONCAT with line breaks.
4. CONCAT with hyphen.
5. CONCAT with semicolon.
6. CONCAT and TEXT function.
7. CONCAT with CHAR function for line breaks.
8. Generate current time and CONCAT.
9. UPPER/LOWER with CONCAT.
10. CONCAT numeric values.

---

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: CONCAT text from a column with space:

To join text from a column with space, the formula is =CONCAT(text1, ” “, text2, ” “, ..textn).

Example 2: CONCAT function with Dates:

To join texts from different cells where there is a combination of normal text and dates, we need to use TEXT function to convert dates into TEXT because CONCAT function combines text strings. The formula is =CONCAT(C6, CHAR(10), TEXT(D6, “mmm-dd-yyyy”)), which joins text and dates.

Example 3: CONCAT with line breaks:

To join text with line breaks, use CHAR(10) for every line break. See the image below for example.

Example 4: Join text strings with hyphen:

To join text with hyphen, use the “-“ in between text strings as shown below.

Example 5: Join text with semicolon (;):

To join text strings and separate them with semicolons, use ” ; ” in between the texts as shown in the image below.

Example 6: Join text and numbers with combination of TEXT and CONCAT:

To join text strings and numbers, use CONCAT function combined with TEXT function. TEXT function combines numbers into text. See the image below for example.

Example 7: CONCAT with CHAR function: Please see the example 3 above.

Example 8: Generate current time and CONCAT:

The formula is =CONCAT(“Today: “, TEXT(NOW(), “mm-dd-yyyy hh:mm AM/PM”)).

Example 9: UPPER/LOWER with CONCAT:

To join text strings and generate the UPPER case letters or LOWER case letters, simply nest the CONCAT within UPPER or LOWER as shown in the image below.

Example 10: Join Numeric Values with CONCAT:

To join numbers, see the example below.

More related posts:

official post

CONCAT Function-PART I

SUBSTITUTE Function

TRIM Function