The CONCATENATE function in Excel is used to combine text from different cells or strings into one cell or string. You can use this function to join names, addresses, dates, numbers, or any other text values. The syntax of the CONCATENATE function is:
=CONCATENATE(text1, text2, ..., text_n)
where text1, text2, …, text_n are the text values that you want to combine. You can enter up to 255 text arguments, and the total length of the resulting text cannot exceed 32,767 characters.
Step by Step
To use the CONCATENATE function in Excel, follow these steps:
- Select a cell where you want to enter the formula.
- Type
=and thenCONCATENATE(. - Select the first cell or type the first text value that you want to combine, and then type a comma (,).
- Repeat step 3 for each additional text value that you want to combine, separating them with commas.
- Type a closing parenthesis
)and press Enter.
Example
Suppose you have a table with the first names, last names, and email addresses of some customers, as shown below:
| First Name | Last Name | |
|---|---|---|
| John | Smith | john.smith@example.com |
| Jane | Doe | jane.doe@example.com |
| Bob | Lee | bob.lee@example.com |
You want to create a new column that combines the first and last names with a space in between, and another column that combines the email addresses with a semicolon (;) in between. You can use the CONCATENATE function to do this, as shown below:
| First Name | Last Name | Full Name | Email List | |
|---|---|---|---|---|
| John | Smith | john.smith@example.com | =CONCATENATE(A2, ” “, B2) | =CONCATENATE(C2, “;”) |
| Jane | Doe | jane.doe@example.com | =CONCATENATE(A3, ” “, B3) | =CONCATENATE(C3, “;”) |
| Bob | Lee | bob.lee@example.com | =CONCATENATE(A4, ” “, B4) | =CONCATENATE(C4, “;”) |
The result will look like this:
| First Name | Last Name | Full Name | Email List | |
|---|---|---|---|---|
| John | Smith | john.smith@example.com | John Smith | john.smith@example.com; |
| Jane | Doe | jane.doe@example.com | Jane Doe | jane.doe@example.com; |
| Bob | Lee | bob.lee@example.com | Bob Lee | bob.lee@example.com; |
Other Approaches to Combine Text in Excel
Besides the CONCATENATE function, there are other ways to combine text in Excel, such as:
- The
&operator: You can use the&operator to join text values, for example,=A2 & " " & B2will produce the same result as=CONCATENATE(A2, " ", B2). - The TEXTJOIN function: You can use the TEXTJOIN function to join text values with a specified delimiter, for example,
=TEXTJOIN(";", TRUE, C2:C4)will produce the same result as=CONCATENATE(C2, ";", C3, ";", C4, ";"). The TEXTJOIN function also has an option to ignore empty cells, which the CONCATENATE function does not have. - The CONCAT function: You can use the CONCAT function to join text values from a range of cells, for example,
=CONCAT(C2:C4)will produce the same result as=CONCATENATE(C2, C3, C4). The CONCAT function is similar to the CONCATENATE function, but it can handle more than 255 arguments and it does not have a character limit.
