Concatenation in Excel

What is Concatenation?

Concatenation is the process of joining two strings together. It combines the text, strings, and data in a series pattern without any gap. There are two ways to combine the data such as,

Concatenation
Merging cells

Merging the cells combines one or more cells and represents them in a single cell.

Concatenation in Excel

In Excel, the data entered sometimes needs to be added and separated. The data is only sometimes present in a structured way; hence, adding or separating the text from one cell to another, rows, and columns is needed. Combining data such as names, addresses, and times is done with the help of the respective Concatenation formula in Excel.

Why Concatenate Data?

Concatenation helps to represent the data in an organized way, which helps the user to go through the data quickly.

If the data contains names of various persons, which is present in column A, and their surnames are present in Column B, the concatenation function combines the name with surnames, making the data organized. Similarly, data like email and address can be combined.

The concatenation function is used to combine text and numbers.

Data like purchase, order Id, and delivery status of every person can be combined, which helps for reference.

Formulas used to Concatenate the data

Excel provides various formulas to concatenate the data. The formulas are explained as follows:

1. Concatenation Function

The Concatenation function combines different pieces of strings or text in a single cell. This function supports Excel 2007-365.

The syntax of the Concatenation function is as follows:

CONCATENATION (text 1,[text 2],....)

The argument text refers to the text string, the value defined by a formula, or a cell reference.

To use the Concatenation function, the steps to be followed are:

Enter the data in the worksheet, namely A1 and B1
In cell C1, enter the formula as = CONCATENATE (A1,",",B1).Press Enter. The Concatenation function combines the data present in cells A1 and B1.

Here the string "Hello Google" is combined using the Concatenation function and displayed in cell C1.

Points To Remember

The essential points to be noted to get the correct result using the Concatenation function are as follows:

The Concatenation function requires at least one "text" argument to work.
The Concatenation function is added with 255 strings, with a total of 8192 characters.
The Concatenation function returns the result as a text string, even though the source values are all numbers.
It returns the #VALUE error when one of the arguments is invalid.
The CONCATENATION function does not recognize arrays. Each value should be entered separately in the formula. For example, the formula CONCATENATE (A1, A2, A3) is correct than CONCATENATE (A1:A3)

"&" Operator

The Ampersand (&) operator is used to concatenate cells in Excel. It is one of the easiest and fastest methods.

The steps to use Ampersand (&) symbol are as follows:

Enter the data in cells A1 and B1

In cell C1 enter the formula as =A1&" "&B1. Press Enter. The result will be displayed as follows,

Concatenating the data without a separator

To combine the data without any delimiter, the formula used is:

=CONCATENATE (A1, B1) either the formula used is,

=A1&B1

The steps to use these formulas are as follows,

Enter the data in cells A1 and B1

In cell C1 enter the formula as, =CONCATENATE (A1, B1).Press Enter, and the result will be displayed as follows,

The result is displayed without any delimiter.

In D1, enter another formula as=A1&B1.

Concatenating the data with a separator

To combine the data with a delimiter such as commas, spaces, punctuation marks, and other characters such as hyphens or slash, the formula used is:

To concatenate the data using space, the formula used is:

=CONCATENATE (A1," ", B1)

Another formula used is:

=A1& " "&B1

To use the formula, the steps to be followed are:

Enter the data in the worksheet, namely A1 and B1

In cell C1 enter the formula as, =CONCATENATE (A1, " ", B1).Press Enter, and the result will be displayed as follows,

In D1 enter another formula as=A1&" "&B1.

To concatenate the data, using commas, the formula used is:

=CONCATENATE(A1,",",B1)

=A1 &","&B1

The steps to be followed are:

Enter the data in the worksheet, namely A1 and B1.

In cell C1, enter the formula as ,= CONCATENATE(A1,",",B1).Press Enter, and the result will be displayed as follows,

Enter another formula in the cell D1 as =A1 &",&B1

To concatenate two cells using a hyphen, the formula used is:

=CONCATENATE(A1,"-",B1)

=A1&"-"&B1

The steps to be followed are:

Enter the data in the worksheet, namely A1 and B1.

In cell C1, enter the formula as ,= CONCATENATE(A1,"-",B1).Press Enter, and the result will be displayed as follows,

Enter another formula in the cell D1 as =A1 &"-"&B1

Notes

The user can use the TEXTJOIN function to concatenate the strings from multiple cells with the required delimiter in Excel version starting from 2019 to Microsoft Excel 365,

Combining the cell value and text string

Excel provides a Concatenation formula to combine the cell value and a specified text string to make the result more meaningful and organized. Sometimes the data must concatenate the specific text with the combined cell values.

The steps to be followed to concatenate the cell value and text string is:

Enter the data in worksheets A1 and B1

In cell C1 , enter the formula as =CONCATENATE(A1," ",B1,"completed")

To add space for the cell value, the formula used is:

=A1&" "&B1& "completed"

Enter the formula in the cell C1 as =A1&" "&B1& "completed". Press Enter. The result will be displayed as,

The user can add the text string before the concatenation cell value and string, the formula used to add the text before the text string is as follows:

=CONCATENATE("The",A1," ",B1)

Another formula used is,

=CONCATENATE("The " & A1 &" "&B1)

Enter the formula in the cell C1 as =CONCATENATE("The" &A1&" "&B1). Press Enter, and the result will be displayed as follows:

Combining the text string with formulas

The user can add text strings with formulas to display the data more attractively based on the data requirement.

For example, to display today's date using the CONCATENATION function, the steps to be followed are:

Enter the formula in the cell A1 as= CONCATENATE("Today is",TEXT(TODAY(),"mmmm d, yyyy"))

The formula concatenates the text with the TODAY() function to display today's date.

To change the format of the date, the formula is modified as,

=CONCATENATE("Today is"& TEXT(TODAY(),"dd-mmm-yy")

Enter the formula in cell A1, and press the Enter Key. The result will be displayed as follows,

Concatenating the Columns

To concatenate the selective data present in two different columns, the step to be followed are:

Enter the data in the respective cell range, namely A1:B5

In cell C1 , enter the formula as =CONCATENATE(A1," ",B1).Press Enter, and the result will be displayed as,

To display the data for the remaining cells, drag the fill handle toward cell C6.

Another formula to concatenate the columns is,

=CONCATENATE(A1&" "&B1)

Enter the formula in cell D1 as =CONCATENATE(A1&" "&B1). Press Enter, and the result will be displayed as follows,

The fill handle method is used to get the result for the remaining cells.

Combining the range of cells in Excel

To combine the range of cells or multiple cells in Excel, the steps to be followed are:

Enter the data in the cell range A1:D4

In E5 enter the formula as , =CONCATENATE(A1,A2,A3,A4).Press Enter, and the result will be displayed as follows:

Another formula to concatenate the data is as follows:

=CONCATENATE(A1&B1&C1&D1)

Enter the formula in cell B1; the result will be displayed as follows:

Combining text and Numbers

The CONCATENATE function can combine the text string with a number based on the data requirement. The combination of number and text can be displayed in both ways; either the format of the number is displayed as it is, or the format of the number is changed. The TEXT function is used to combine text and numbers. The steps to be followed are:

To combine TEXT and NUMBERS, where the number is displayed with two decimal places with a $ sign,