How to use “CONCATENATE” function in Excel

While working with Microsoft Excel, did you get a situation to combined multiple strings into one? I believe most of your answers are YES. In the said scenario “CONCATENATE” function will help you to find the solution. We will be learning the Microsoft Excel “CONCATENATE” in details, so stay with us and continue reading…

CONCATENATE function is used for combining two or more Microsoft Excel strings into one. The output of the function returns as a combined string in new cell.

Function should give output in “General” format, however if output is not as per the desired format then we need to change the cell format to “GENERAL”.

CONCATENATE Function has argument called “text”, where we need to give the cell references, we can give multiple cell references as per the requirement to combine by following the “ , “ (i.e. Comma) as separator.

If strings are not separated by “ , “ (i.e. Comma) or quotation mark is/are missing in the function then it will give output as “#NAME?” (Error). So always ensure that quotation marks are provided while using the function to get the appropriate results/output.


Advantage of “CONCATENATE Function”

CONCATENATE function is very advantageous in many ways. It helps for the document where combining multiple strings or creating a “Unique Key” is required. Combining multiple strings or writing “Unique Key” manually (one by one) in a report is very difficult and CONCATENATE Function helps to apply the function in large database at once and makes the work easy, saves time and increases efficiency.

Where “CONCATENATE ” function can be used:

CONCATENATE Function is very useful and can be used in many places. Like it can be used as follows:

– Creating a “UNIQUE KEY” for a document for lookup purpose

– Combining Personal Details i.e. First Name, Last Name to “First Name Last Name” together

– Or any other database where there is requirement of combining strings, CONCATENATE Function” can be used

Syntax Description:

text1 argument, is used to give the cell reference of the first string, which is to be combined.

[text2] argument, is optional argument and is used to give the cell reference of next string

…, represents that, we can give multiple [text] strings as per the requirement, by following the “ , “ (i.e. Comma) as separator.


Things to Remember:

– Always ensure that the formula cell should be formatted to “GENERAL”

– If strings are not separated by “ , “ (i.e. Comma) or quotation mark is/are missing in the function then it will give output as “#NAME?” (Error).

Example 1: Preparing a “UNIQUE KEY” for lookup purpose

CONCATENATE function is more often used to create a “Unique Key” in the document that helps for various lookups. We will create a Unique Key in a document to understand the function better:

Concatenate Function - ExcelSirJi

Explanation: “Unique Key” is created in the database, for example Cell A2 and A3 has same Product Code i.e. 1218 however location given in cell B2 and B3 are different i.e. A and C, that is why Unique Key is created for Product Code in cell C2 (1218A) and C3 (1218C)

Example 2: Combining Personal Details from a User Form Database

Here, we have customer database where “First Name” and “Last Name” is given in two different columns and we have a requirement of combing the “First Name” and “Last Name” together. We will follow as below:

Concatenate function excel tutorial

Explanation: “Complete Name” field is now created in the database with the help of function.

Kindly note,  if we simply add cell A2 (i.e. Amy) and cell B2 (i.e. Lam) then output would have been “AmyLam”, which is not in correct format. That is why additional space i.e. “ “ is added in text2 argument to make the string in desirable format.

Hope you learnt this Function,

Don’t forget to leave your valuable comments!

If you liked this article and want to learn more similar tricks, please Subscribe us or follow us on Social Media by clicking below buttons:

Similar Posts

Leave a Reply

Your email address will not be published.