Excel CONCAT function - string concatenation examples

This tutorial explores the Excel CONCAT function, a streamlined way to combine text strings from multiple cells. It also highlights key differences from its predecessor, CONCATENATE. Learn to concatenate with various delimiters (spaces, commas, line breaks), handle dates, and integrate with other functions.

Excel's CONCAT function efficiently merges text strings from different cells or ranges into a single cell. It's a Text function available in Excel 365, 2021, 2019, and online versions.

The syntax is straightforward: CONCAT(text1, [text2], ...) where text1 is required and subsequent text2 etc. are optional strings. Arguments can be text values, cell references, or ranges (up to 255). Concatenation follows the argument order.

For instance, combining "john" and "@gmail.com": =CONCAT("john","@gmail.com")

Key CONCAT Function Considerations:

1. At least one text argument is needed. Invalid arguments (e.g., error-producing functions) cause errors.
2. The output is always a text string, even when numbers are involved.
3. Strings exceeding 32,767 characters result in a #VALUE! error.
4. CONCAT doesn't have a built-in delimiter option; delimiters must be included manually within the formula.
5. Empty cells are ignored.

Practical CONCAT Examples:

• Combining Cells: Concatenate cells A2 and B2 using =CONCAT(A2, B2). Merging A2:C2 can be done with =CONCAT(A2:C2) or =CONCAT(A2,B2,C2). Blank cells are automatically omitted.

  

• Combining Columns: Concatenate columns A, B, C row-wise in column D using =CONCAT(A2:C2) in D2, then drag down.

  

• Concatenating Ranges: Combine cells A2:C3 with =CONCAT(A2:C3). Non-adjacent ranges are also supported: =CONCAT(A2:C3, A5:C6).

  

• Concatenating an Entire Column: Join all cells in column A using =CONCAT(A:A) (or =CONCAT(A2:A1048576) to exclude headers). Remember the 32,767-character limit.

  

• Using Delimiters: Add commas or spaces between concatenated strings. For example, =CONCAT(A2, ", ", B2, ", ", C2) adds commas and spaces. Use CHAR(10) for line breaks (remember to enable Wrap Text).

  

• Concatenating Dates: Convert dates to text using the TEXT function before concatenation: =CONCAT(A2, CHAR(10), TEXT(B2, "d-mmm-yyyy")).

  

• Combining with Other Functions: Nest other functions within CONCAT. For example, =CONCAT("Current time: ", TEXT(NOW(), "hh:mm AM/PM")) adds the current time.

  

CONCAT vs. CONCATENATE:

CONCAT supersedes CONCATENATE, offering improved range handling. CONCATENATE is retained for backward compatibility. CONCAT is available in newer Excel versions only.

Downloadable practice workbook available (link omitted as per instructions).

The above is the detailed content of Excel CONCAT function - string concatenation examples. For more information, please follow other related articles on the PHP Chinese website!