How To Use Microsoft Excel Formula "CONCATENATE"

Use of  "CONCATENATE" Formula

Formula:  "CONCATENATE”

In Microsoft Excel, CONCATENATE is a function, by which we can join two or more strings into a single cell. It can be used to combine text strings, numbers, or cell references, and can be useful for creating longer strings of text or for formatting data in a specific way.

The syntax for the CONCATENATE function is as follows:

=CONCATENATE (TEX1,TEX2, TEX3, TEX4 ................)

Explanation: CONCATENATE is a very useful function in Microsoft Excel. We can join up to 30 texts together at a time. We can use CONCATENATE within the sheet, different sheets, and different workbooks. Let's suppose we have huge data with employees' names and we want to add Mr. Ms. Miss etc, before the names. It is not easy and time consuming to add Mr. or Miss. one by one. But there is a formula named CONCATENATE, could help to reduce time taking work do your work easily and very quickly.

In order to understand please follow the simple steps.

Step: 1 - Let's take an example of raw data as below table. We have huge data from the company employees.

Report: A

Step: 2 - We have to add Mr. Or Miss. before the names. We can do it by two different methods. Please read both methods to understand the logic of the formula.

Methods: 1- You can put Mr. or Miss inside the formula if you already know that all staffs are male or female.  Compare Report A & Report B and find the value as per your requirement.

 

Report: B

Formula Used Explanation: =CONCATENATE("Mr.", " " , G21)

"Mr."  --  We have used Mr. inside the formula because we have already known that all employees in the database are male-only. But if you don't know all are male or female, so you can not use Mr. inside the formula.

 " "  : This inverted commas is for space between Mr. and Name. If we do not use this inverted comma and space we will find a result without space like Mr.Petter Lawrance.

 G21: This is the second text reference cell. Let suppose we want to add Mr. with Peter Lawrence, so Peter Lawrence's cell value is G21.

Methods: 2- In method 2 you must add extra input Mr. or Miss according to gender. You first make sure about the gender.

 

Report: C

Formula Used Explanation: =CONCATENATE(H21, " ", G21)

H21 : Now we are using cell reference in place of Mr. or Miss. H21 is the extra input cell reference or you can say it is text1 for the formula.

 " " : It is used for extra space between Mr. and Name, as we mentioned above.

G21: Ad discussed above.

Now we can see the difference between Report B and Report C. Once we understood the logic of the formula we can use it anywhere.

Learning Tips:

* You can add up to 30 texts.

* Prepare the same table in your to excel and try again and again to clear your doubt.

Please leave your comment: for more excel queries.
Thanks for your time.

How To Use Concatenate in Excel | Concatenate Formula | Concatenate function | Concatenate uses | How Concatenate Function Works | What is the use of Concatenate function | What is the use of Concatenate Formula | Use of Concatenate in Microsoft Excel