How to Use Excel’s CONCATENATE Function: Combine Text with Spaces and Line Breaks

スポンサーリンク
スポンサーリンク

About the CONCATENATE Function

Overview of CONCATENATE

Combine Text Strings Excel Function/Text Operations

=CONCATENATE(Text1, Text2, Text3,…)

Summary: Combines multiple text strings separated by commas.

  • You can also use the & operator to concatenate text: =(Text1 & Text2).
  • Concatenation with the + operator results in a #VALUE! error, as it is not valid for text strings:
    CONCATENATE(Text1, Text2) (Text1 & Text2) (Text1 + Text2)
    Text1 Text2 Text1Text2 Text1Text2 #VALUE!
  • You can directly concatenate quoted text strings using commas.
  • Blank cells are included in the concatenation without modification.
  • CONCATENATE does not support range-based concatenation (e.g., A1:A5). Use CONCAT (available in Excel 2019+) for that functionality.
  • For specifying delimiters, use TEXTJOIN, which is available in Excel 365.
  • Because Office 2016 is still widely used, CONCATENATE is a better choice for compatibility when sharing Excel files.

When to Use CONCATENATE

  • To combine multiple strings into one.
  • To merge first and last names into a single cell.
  • To join city and state names from separate cells into one cell.

How to Use CONCATENATE

  A B C D E
1 John Doe JohnDoe

=CONCATENATE(B1, C1)

=(B1 & C1) can also be used.

2
3

Combining Dates with CONCATENATE

  • Combining dates directly with CONCATENATE will result in the date’s serial value being concatenated. For example, “44978” (2/21/2023) and “44979” (2/22/2023) will result in “4497844979”.
  • To display formatted dates, use the TEXT function to convert them into strings before concatenation: =TEXT(A1, “mm/dd/yyyy”).

Combining Strings with Line Breaks

  • Insert line breaks using CHAR(10) as part of the concatenation formula.
  • Ensure the target cell has “Wrap Text” enabled (Home → Alignment → Wrap Text).

Limitations of CONCATENATE

  • CONCATENATE cannot concatenate ranges (e.g., A1:A5). Use CONCAT for this.
  • CONCATENATE has a character limit of 255 characters. If exceeded, split the concatenation into smaller chunks.