CONCAT Function

CONCAT function in Excel has the ability to combine data from different cells. It is an essential function that can save time and improve data readability. The CONCAT in Excel is a must-know to streamline data organization.

Syntax of CONCAT Function in Excel

=CONCAT(text1, [text2], [text3], ...)

Argument	Description
text1 (required)	The first text item to join using the CONCAT function. This could be a cell reference (like A1), a text string (like "Hello"), a comma (like “,”), or another function.
text2 (required)	The second text item to be joined.
text3,.... (optional)	Additional text items that you want to combine with text1 and text2.

Each argument can include a cell reference, hardcoded text within quotation marks, or a function. You can include up to 255 text arguments.

Note: The CONCAT function is a simple, updated function of the older CONCATENATE function.

Other Interpretation of CONCAT Function

=CONCAT(cell1, delimiter, cell2)

Example:
If A2 contains Jamie and B2 contains Cole, using =CONCAT(A1," ", B1) will return Jamie Cole.
A space enclosed in " " is the delimiter.

Examples of CONCAT Function

Combining Two Text

Combine First Name and Last Name to get Full Name.

• Formula: =CONCAT(A2," ", B2)
• Description: It combines the value in A2 (Alex) and the value in B2 (Grant), separated by a space ” “.
• Output: Alex Grant

Combining Text with Numbers

Write a statement using a person’s name and their score in a single cell.

• Formula: =CONCAT(A3," Scored ",B3," Marks.")
• Description: It combines the values in A2 (Alice) and B2 (50) along with the mentioned strings " Scored" and "Marks.".
• Output: Alice Scored 50 Marks.

Merging Dates with Text

Include a date in a sentence:

• Formula: =CONCAT("Report for ", TEXT(A4,"dd-mm-yyyy"))
• Description: It combines the string “Report for ” and the date is A4 (25-05-2024) by converting it to text using the TEXT function.
• Output: Report for 25-05-2024

Combine Text, Symbol and Function

Calculate the sum of a range of cells and provide the total value with text and number value with a currency symbol.

• Formula: =CONCAT("Total: ","$",SUM(c2:C4))
• Description: The sum function enclosed within the formula adds the values in the mentioned range and combines them with the string "Total: " and "$" sign.
• Output: Total: $652

Alternatives for CONCAT Function

Since Excel 2016, CONCATENATE has been replaced by CONCAT and TEXTJOIN.

Using TEXTJOIN

The TEXTJOIN function provides even more flexibility, allowing you to add delimiters automatically:

• Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
  • Delimiter: The character(s) to place between each text item.
  • Ignore_empty: Set to TRUE to ignore empty cells.
  • text1, text2, …: The cells/range to join.

Example: =TEXTJOIN(", ", TRUE, A1:A5) combines cells from A1 to A5, separated by commas, ignoring empty cells.

Common Errors While Using CONCAT

#NAME? Error

It occurs if CONCAT is misspelled. Ensure the function is spelled correctly or try CONCAT.

#VALUE! Error

If any argument is an invalid data type, the function may return #VALUE!. Double-check all arguments.

Missing Spaces or Delimiters

If items are combining without spacing, review the formula for missing " " separators or try TEXTJOIN for automatic delimiters.

CONCAT Function – Tips and Best Practices

• Using Space Between Words: While combining two cells (like first and last name), include a space " " as one of the arguments.
• Avoiding Errors with Blank Cells: Use TEXTJOIN with ignore_empty set to TRUE to avoid extra spaces from empty cells.
• Formatting Dates and Numbers: Use the TEXT function to format numbers and dates within CONCAT.

Conclusion

The CONCAT function is essential for simplifying data entry, report creation, and list management in Excel. While newer functions like TEXTJOIN offer enhanced functionality, understanding CONCAT builds a solid foundation for mastering text manipulation in Excel.

Frequently Asked Questions (FAQs)