Concatenate Formula in Microsoft Excel

In this article, we’ll learn about CONCATENATE function in MS-Excel.

It is a text function which is used to merge two or more text strings from different cells in a same cell.

In Excel 2007 and later versions, you could take up to 255 text arguments. However, in Excel 2003 function only accepts up to 30 text arguments.  

Let’s take examples and understand how we can use CONCATENATE function in Microsoft Excel.

Refer to the table below with 3 different examples:

image 1

 

1st Example:

In the first example, we want to combine the text of 3 cells: Hello Good Morning

Follow given below steps:

  • Enter the function in cell D8
  • =CONCATENATE(A8,” “,B8,” “,C8)
  • Press Enter
  • Function will return Hello Good Morning

image 2

 

Note: CONCATENATE will combine exactly what it is asked to combine. If we want any punctuation, space or any special character or symbol to be places in between the text strings, we’ll need to ask function to include it.  As we have it in the above example: After selecting Cell A8 and B8, we have added space in inverted commas.

2ndExample:

In the above example, we have learned how to add space. Now, in this example we will learn how to add division slash (/) symbol.

image 3

 

Follow given below steps:

  • Enter the function in cell D9
  • =CONCATENATE(A9,” “,B9,”/”,C9)
  • Press Enter
  • Function will return Drive car at 80 miles/hour

image 4

 

3rd Example:

In this example, we will combine date along with text. Follow the steps given below:

  • Enter the function in cell D10
  • =CONCATENATE(A10,” “,B10,” “,C10)
  • Press Enter
  • The function will return “Starting Date is 42005”, whereas date & time in Excel are represented by numbers and it is displayed as date and time because of cell formatting. Therefore, when date or time is added to the concatenate function, the underlying number value is displayed in the result

image 5

 

So, this is how we can use CONCATENATE function in different ways in Excel.

 

image 48

 

Video: How to use CONCATENATE function in Microsoft Excel

Watch the steps in this short video, and the written instructions are above the video

 

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 
 

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube