Concatenate function in MS Excel

Concatenate Function in MS Excel

Concatenate function in MS Excel is an important and simple function, which can be learned easily. It is used to join two or more strings (Text) to make one string.

Let us understand the syntax first:

Concatenate1

text1: It is a mandatory field. Here we can enter the item (text value, number, or cell reference) which we want to join.

text2: Here we can enter the second item to join.

It would be interesting to know that we can enter up to 255 items, up to 8,192 characters.

Now we will discuss one example to understand use of Concatenate function in MS excel.

Step 1: There is a set of data as shown in below screen shot.

Concatenate

Step 2: Now we want to Concatenate PO and PO item cell. We will insert a new column between PO item and Material Column.

Concatenate1

Step 3: Now apply formula as shown in below screen shot.

Concatenate2

  1. Click on the cell where we want to apply the formula.
  2. Click on formula button. A new pop up window will appear.
  3. Select the Concatenate formula
  4. Click OK button to apply formula.

 

Step 4: A new pop up screen will appear and here we will enter necessary cell numbers as shown in below screen shot. First enter Text 1, then Text2

Concatenate4

in the result Text1 and Text 2 are joined.Press OK button and see the final result on excel sheet.

Concatenate5

Value of both cells joined.

Other then joining values of excel sheet cells. We can also join a cetain Text with the value of a cell.

For example : If we want to add a Text – WORLD after the Material (Column G). Then use this formula:

=CONCATENATE(G3,”WORLD”).

Output will be : ABC123WORLD.

Here you can notice that the additional text is between “”.

If you want to give a space between the two items then use formula in this way:

=CONCATENATE(G3,” “, “WORLD”)

Output will be : ABC123 WORLD.

Vaibhav

Author: Vaibhav

Vaibhav is an engineer by schooling and an SAP Consultant by training with 12 years of experience. He is certified in SAP PM and SAP MM. His expertise includes SAP PM, SAP MM, SAP QM, and MS excel. His industry specialties are Chemical, Automobile, Electronics and Infrastructure. He has lots of experience in SAP Training and Consulting.

How drop down list in excel can help you?   Almost every SAP consultant had
Learn IFERROR in excel in less than 5 minutes Time to say good bye to #N/A,
5 Effortless tricks to handle Duplicates in Excel This is going to be the most
Shortcut keys in MS excel to improve your efficiency at work   Here is another
Innovative use of AutoCorrect option in MS Excel You must be wondering after reading the

1 Comment

  • AJIT SAMBHUS February 7, 2017 Reply

    This is very useful function & helps a lot
    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

error: Content is protected !!