Excel String Concatenation

The concatenation of strings is performed by the two concatenate methods provided by Excel: the & operator and the concatenate inbuilt function.

For example, suppose we use =”Anand”&”Singh” in a cell. As a result, it will give us Anand Singh. Similarly, when we use =Concatenate (“Anand”,”Singh”), it will provide the same result.

Syntax

The syntax is very easy to understand.

  • Text1 is what is the first string we need to concatenate in Excel.Text2 is what is the second string we need to concatenate with Text1.

Like this, we can combine 255 values.

Examples

Example #1 – Basic Example of Concatenating Values into One

For example, in cell A1 we have “Good.” In cell A2, we have “Morning.”

Example #2 – Use the Ampersand (&) Symbol as the Alternative

Why do we want to use the CONCATENATE function where we can use the AMPERSAND (&) symbol as an alternative?

  • First, we must open the concatenate formula in cell A4. Then, we must select cell A1 as the first Text1. Next, select cell A2 as the second argument (Text2). We have only two values to combine. So close the bracket and press the “Enter” key. Oh, hold on.We have selected two values above, so the concatenate function combines these two values. So, while applying the formula after selecting Text1, we must insert a space character as the second value to be concatenated before choosing Text2. After selecting “Text1” in the “Text2” argument, we need to supply the space character in double-quotes as ” “. Once the space character is inserted, we will get the following result.

Oh, hold on.We have selected two values above, so the concatenate function combines these two values. So, while applying the formula after selecting Text1, we must insert a space character as the second value to be concatenated before choosing Text2.

We can use an ampersand symbol to concatenate values. For the same example of combining “Good Morning,” we can use the formula below.

After each value, we need to put the ampersand (&) symbol. It is the most commonly used method to concatenate values in Excel. However, the CONCATENATE formula is not popular among Excel users thanks to the ampersand symbol.

Example #3 – CONCATENATE Cell Values with Manual Values

We can enter our values with cell values, much like inserting space characters. For example, look at the below data set.

We have a zone-wise sales value here. In the sentence column, we need to create a sentence like this –

“Total Sale of East Zone is 1500″

In the above sentence, we have only two values available with cells, i.e., bold values—the remaining values we need to add as our own.

We must insert an equal sign in cell C2 to start the concatenation. As part of our sentence, our first value to be concatenated is “Total Sale of “(including space after).

The next value is our cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more.

The third sentence to be concatenated is “Zone is “.

And the final value is cell reference.

Next, we need to press the “Enter” key to get the answer.

We will drag the formula to get the concatenate values in other cells.

Example #4 – CONCATENATE Date Values

Now, look at one more example of concatenating dateExample Of Concatenating DateTo concatenate a date with other values in Excel, we can use the & operator or the built-in concatenate function. For example, if we use =“ABC”&NOW(), the output will be ABC14/09/2019.read more values. Below is the data.

We need to combine these two values with our sentences. Below is a sample of the same.

“Ramu Joined on 12-Jan-2018″.

We must open an equal sign in cell C2 and select A2 cells.

Next is our sentence, so we must enter the text in double quotes.

The last cell that needs to be concatenated is “Date.” So, we must select the B2 cell.

Now, press the “Enter” key.

We were supposed to get 12-Jan-2018. Instead, we got 43112.

It is the situation faced by whoever has learned the basic concatenation level. When concatenating date, time, and numbers with formatting, we need to apply the formatting with the TEXT function in excelTEXT Function In ExcelTEXT function in excel is a string function used to change a given input to the text provided in a specified number format. It is used when we large data sets from multiple users and the formats are different.read more. So, we must select the TEXT function while selecting the “DATE” cell.

Now, we must mention the format as “DD-MMM-YYYY.“

Then, press the “Enter” key to get the answer.

We need to apply the TEXT function to the given format as a date because Excel stores date and time as serial numbers. So whenever we combine, we need to provide formatting to them.

Now, drag the formula to get the concatenate values in other cells.

This article has been a guide to Concatenate Strings in Excel. We discussed the concatenating strings using concatenate formulas, various methods, examples, and a downloadable Excel template. You can learn more about Excel functions from the following articles: –

  • How to do the Opposite of Concatenate in Excel?Excel CONCATENATE FormulaConcatenate Columns in ExcelConcatenate Strings in VBA