CONCAT function in Excel is an essential tool for anyone looking to streamline their data processing and reporting workflows. CONCAT replaces the CONCATENATE function.
These functions are incredibly useful for merging text strings, combining contents from multiple cells, and including additional characters like spaces or commas for clarity and readability in your Excel spreadsheets.
In Excel 2019 and earlier versions, the CONCATENATE function was widely used to combine text from different cells. For example, to merge the text from cell A1 and B2 with a space in between, you would use the syntax =CONCATENATE(A1, " ", B2). The quotation marks are used to specify that a space should be inserted between the text from A1 and B2. This function allows for multiple text arguments, enabling you to combine text strings, cells, and additional characters like commas and spaces as needed.
However, starting from Excel 2016, Microsoft introduced the CONCAT function as a modern, more flexible alternative to CONCATENATE. The CONCAT function does not require you to use the ampersand (&) operator for concatenation, which simplifies the syntax for combining multiple cells. For instance, to achieve the same result as the earlier CONCATENATE example, you would use =CONCAT(A1, " ", B2). This new function supports a range of text arguments and directly combines them, making it easier to work with especially when dealing with large datasets or needing to concatenate text from an array of cells.
For scenarios requiring the insertion of specific delimiters, such as a comma and a space, you can include these directly within the text arguments using quotation marks. For example, =CONCAT(A1, ", ", B2) would combine the contents of A1 and B2 with a comma followed by a space in between.
Topics covered:
- What is CONCAT Function?
- Argument and description of CONCAT Function.
- How to use the CONCAT function in Excel
- Concatenate a range of cells.
- Concatenate all cells in the column.
- CONCATENATE cell with space and comma.
1. What is CONCAT Function and its Syntax?
The CONCAT function is used to combine multiple text strings into a single string or combine text of multiple cells into one cell. It’s available in various spreadsheet and programming languages, with slightly different syntaxes depending on the specific software. Here are some common examples:
Syntax: =CONCAT(text1, [text2], …)
2. Argument and description of CONCAT / CONCATENATE Function.
| Argument | Description |
| text1 (required) | Text elements to connect.
A string or array of strings, such as a cell range. |
| [text2, …] (optional) | Added text elements to be merged. Text elements can have up to 253 text arguments. They may either be in the form of a string or an array of strings, like containing cellular units. |
3. How to use the CONCAT function / CONCATENATE in Excel?
There are steps for uses of CONCAT function. Steps are below: –
Step 1: Take a full name as the information and create a data table with the name in different two columns in A2 and B2 as shown below.
Written the name here.
Step 2: Add a column C1 and C2 to get the Concatenate output there.
Added the column here.
Step 3: You can refer to the concatenate function and refer to A2 as well as B2 for the formula. The formula is: =CONCATENATE(A2,B2)
Used the formula here.
Step 4: In the end, hit enter to get the CONCAT output.
Here is the result below. (There is a space in column A after the name)
4. Concatenate a range of cells through Excel CONCATENATE Function.
Step 1: Make a table with some random numbers as 1-10.
Placed the numbers here.
Step 2: Add another column C1 and D1 to get the Concatenate a range of cells there.
You can see below the column has been added here.
Step 3: Here, you want to get the result of A2:A8 cell range. Enter the formula now. The formula: =CONCATENATE(A2,A3,A4,A5,A6,A7,A8)
Applied the formula here.
Step 4: After entering the formula press enter button.
Here is the result is shown below.
5. Combine data using the CONCAT of all cells in the column.
Step 1: Pick some names and some letters as the information and put them into your excel sheet as shown.
The information is written down in the Excel sheet below.
Step 2: To get the result you need to add another column in C1 and C2.
The Column is added here.
Step 3: Here you can refer to the concatenate formula to get the different cells in one column. Here you can select A1:A7. The formula: =CONCATENATE(A2,A3,A4,A5,A6,A7)
Placed the formula here.
Step 4: Click on the Enter button to get the result.
The result is outlined below.
6. Important facts you should keep in mind about CONCAT Function.
To ensure that the CONCATENATE function always returns correct results, you must follow several rules.
- The CONCATENATE function must contain at least one “text” argument to work.
- The formula returns #VALUE error when the CONCATENATE function argument is not valid!
- The Excel CONCAT function or CONCATENATE function’s output is always a text string, regardless of the source values.
Application of CONCAT function in Excel
- Merging Names: Use CONCAT to combine first and last names from separate columns into a single full name column, enhancing readability and organization.
- Creating Email Addresses: Employ CONCAT to assemble email addresses from parts stored in different columns, such as combining user names and domain names for a complete email list.
- Address Compilation: Utilize CONCAT to stitch together street, city, state, and zip code data from separate columns into one full address field, aiding in mailings and reports.
- Generating Unique Identifiers: Apply CONCAT to merge various pieces of information, like date, name, and ID numbers, to create unique identifiers or keys for database management.
- Data Categorization: Use CONCAT to combine category-related information from multiple columns, facilitating more efficient sorting, filtering, and analysis of data sets.
- Dynamic Formula Creation: Employ CONCAT to dynamically assemble text for formulas or references based on the data in other cells, enabling more flexible and dynamic Excel functions.
Both CONCAT and CONCATENATE are powerful tools in Excel for text manipulation, enabling you to dynamically create strings that can include data from multiple cells along with fixed characters for formatting. Choosing between them largely depends on your version of Excel and specific project requirements, with CONCAT offering a more streamlined approach in newer versions.
You may be interested:
