CONCATENATE in Excel – Shiksha Online

The article discusses the use of CONCATENATE in Excel to combine strings.

The CONCATENATE function in Excel allows us to join two or more text strings in the same cell. It is very useful in managing databases and gives a unique reference to the cells that identify each record. CONCATENATE is a function with a relatively simple implementation that supports matching up to 255 text strings.  

Content

• Using CONCATENATE Function in Excel
• CONCATENATE in Excel with Spaces
• CONCATENATE Function with Comma
• Adding a Function to CONCATENATE Formula
• Points to note

Category: Text Functions

What is it for?: The CONCATENATE function combines two or more text strings to create a single text string.

Syntax:

=CONCATENATE(text1, [text2],…)

Where –

text1: This is a required argument. The element can be a text value, number, or cell reference.

text2: This is an optional argument. There can be a maximum of 253 text arguments for the text items. Each can be a string or an array of strings.

Must Explore – MS Excel Courses

Using CONCATENATE Function in Excel

CONCATENATE function in Excel to join texts and numbers. If we want to use the CONCATENATE function to join numbers and letters, we must consider that numbers do not need to be enclosed in quotes, but text values ​​do.

In the below example, we have different digits in different columns.

To combine them all, we will use the formula – 

=CONCATENATE(A1,B1,C1,D1,E1)

You can see that the strings have been joined into one.

Read MS Excel Tutorials

CONCATENATE in Excel with Spaces

We must replace the dashes in the above formula with spaces, keeping the quotes. Otherwise, Excel would interpret it as an error and remove the space.

For example, if we want to use the CONCATENATE function to say the following sentence: “I am 30 years old”.

So if you use the same formula that we used above, which is –

=CONCATENATE(A1,B1,C1,D1,E1)

The results would look something like this, which looks incorrect.

Here, we need to tweak our formula a little and add spaces. The formula we will use will be:

=CONCATENATE(A1, ” “, B1,” “, C1,” “, D1,” “, E1)

As you can see, we put two quotes and a space between the arguments so that the space exists in the result; otherwise, “I am 30 years old” would appear altogether.

CONCATENATE Function with Comma

The CONCATENATE function can help us in cases where the information is distributed in several columns, and we want to integrate the information into a single column. For example, I have some information spread across different columns. 

This is the case where we will consider a comma as an argument and include that in our formula.

I will start writing the CONCATENATE function, specifying my arguments:

=CONCATENATE(A1,” “,B1,” “,C1,” “,D1,E1,” “,F1,” “,G1, H1,” “, I1,” “,J1, ” “, K1),

Note that I have included a space after every cell number, and where the comma appears, no space is mentioned as we don’t want a space before the comma.

After we apply this formula, we get the result as follows.

It is important to note that the CONCATENATE function does not generate blanks between the strings, so we must put them.   

Adding Function to CONCATENATE Formula

We have a dataset of the employee names. If we apply the formula using spaces within the quotes, the characters will become a string, but double spaces will appear in cases where there is no middle name.

You can see that the empty cells appear as double spaces because of the space we entered in the formula. To avoid such problems, we would need to add logical functions to the formula so that Excel evaluates if there is an empty cell. If so, it does not include one of the spaces we are using, nesting the concatenate function with the if function.

We use the logical function IF. the formula would be –

=IF(B2=””,CONCATENATE(A2,” “,C2),CONCATENATE(A2,” “,B2,” “,C2))

The formula used in the example evaluates if cell B2 is equal to empty and if so, it simply concatenates the first name (A2), with the first and second last names (A2) and (C2); in case it contains any data, cell B2 is included in the formula.

You can see that the logical function ignores the empty cell and calculates as per the condition given in the formula.

Points to Note 

• CONCATENATE function is replaced with the CONCAT function in the latest versions of Excel. The earlier function is still retained for backward compatibility.  
• If the arguments in the CONCAT function are invalid or incorrect, the function will return #VALUE!, which is an error

Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel