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.
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:
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
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
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.