How to Perform Basic Concatenation Functions in MS Excel

Supriti Tripathy

23rd Jul'21

MS Excel provides a spreadsheet to record, store and analyze large amounts of data systematically. There are individual cells that let us enter numbers, text, and symbols to use them later. However, sometimes we do not get the data structure as per our needs. We may want results where the records in one cell are displayed in separate cells and vice versa. You can create macros in Excel, prepare Pivot tables etc. However, to get access to all the features enabled by Excel, we must learn some concepts and formulas.

To consolidate two cells, we must understand the basic concatenation function. This function joins text values together and returns a text. It can join up to 30 string values together. It is different from the "Merge function" as the former combines two or more values to display a new deal, whereas the latter combines two or more cells to make one larger cell. Let us look at the formula with the syntax.


Concatenate Function

As mentioned, we use this function to join string values of two or more cells to display one string value.

The format is Concatenate(string1, string2).

It is essential to separate the values by commas, and any string not defined by variable must be enclosed in quotation.

Example: Let us assume that the values in the variables are as below:

string1- Hello

string2- How are you?

When you write the above function, it results in the following output:

Hello, How are you?  

You can use a comma, space, or a punctuation mark to make sense of the text. It depends on the user to modify the input as per the output requirement. In the above example, space can resolve the issue. So, the formula changes to 

Concatenate(string1, “ “, string2)

It is important to note that in Excel 2016 and the subsequent versions, the function is changed to Concat(text1, text2) instead of concatenation. The syntax is all the same.




Variations in Concatenate Function


Concatenating a String and a Cell Value

The concatenation function has many variations and is not limited to only combining two or more given texts. We can play around with the function with cell values and with other formulas. The only condition is all the variable types must be a string.

Example: If values in cells are given as 

A20: Rishabh

A22: Pandey

Then the function Concatenate("The full name of the player is","" ",A20,", "A22,".") will display the result as

The full name of the player is Rishabh Pandey.


Concatenating a String and a Formula

You can work around this function with formulas. Just remember to enable the data type as text.

Example: Concatenate("The date and time of today is ", TEXT(NOW(), "dd-mmm-yyyy hh:mm")) will display the result as 

The date and time of today is 13-May-2021 16:22

We have seen how to include commas, spaces, quotations, and other punctuation within the concatenation function through the above examples.




The Use of Ampersand (&) Operator

The ampersand operator does the same work as the concatenation function. It is easier to type "&" than typing "concatenation." The syntax remains the same. The only difference is that while using the concatenation function, Excel limits the character to 255 strings, whereas in using an ampersand, there is no limitation.

Every user is different and so are their preferences. We can use both the concatenation function and ampersand operator to join two or more text values.

Example: "The date and time of today is "& TEXT(NOW(),"dd-mmm-yyyy hh:mm") will display

The date and time of today is 13-May-2021 16:22

 "Priya is on the project "& "-" & "Home Science."


Using Concatenation with Line Breaks

There will be a minor change in the syntax while using this function for line breaks. You must use "char(10)" to enable a line break in a sentence. Also, while using line breaks with the concatenate function, press Ctrl+1 and check the Wrap Text box in the Alignment option of the Format cells dialog box. 

Example: CONCATENATE(“Rishav," ",”Pant”,CHAR(10),”Nationality”," ",”Indian”,CHAR(10),”Religion”,” “,”Hindu”) will display

Rishav Pant

Nationality Indian

Religion Hindu

To apply the function to a range of cells, drag the function used once to the respective columns.


Important Points to Note when using Concatenate Function

  • It needs at least one text argument.
  • It returns a string value.
  • In a single formula, it allows 255 text strings to combine, that is 8190 characters.
  • You cannot apply it to arrays.
  • All arguments within the function must be valid; else, it will display an error.





Excel is built in with several features like creating accessible documents. The above article shows some of the formulas using the Concatenation function. There are many scopes, and you can use the function to display results over a range of cells and columns. It can display Date, Time, and Number in various formats. The only important thing to remember is to include the "Text" function for any value that does not contain a "string" as the return type of this function is a text value.


We at OpenGrowth, are committed to keeping you updated with the best content on the latest trendy topics from any major field. Also, both your feedback and suggestions are valuable to us. So, do share them in the comment section below.

A believer of good things and pursuer of diverse avocation, she is a fiction lover and a simple writer. Supriti has a number of professions to her list and she feels challenges are the only answers to failures.


Blank ATM Card +393512615163

19th May'22 10:37:20 PM

Get $5,500 USD every day, for six months! See how it works. What'sApp : +393512615163 I want to testify about Dark Web blank atm cards which can withdraw money from any atm machines around the world. I was very poor before and have no job. I saw so much testimony about how Dark Web hackers send them the atm blank card and use it to collect money in any atm machine and become rich. Email : Hangout : What'sApp : +393512615163 They also sent me the blank atm card. I have used it to get 90,000 dollars. withdraw the maximum of 5,000 USD daily. The Dark Web is giving out cards just to help the poor. Hack and take money directly from any atm machine vault with the use of an atm programmed card which runs in automatic mode.