EXCEL CONCATENATE Function [&] to combine cells, columns and strings

In the excel worksheet, you will not find structured data as per your needs. You might want to divide or split the data into different cells. For this, you need to use Excel Split Cell function which is said to be an opposite of concatenate excel. If you want to combine the split data content from two or more than two columns into a single column then you need to use concatenate, ampersand and text join function. Here we are going to discuss everything about excel concatenate function to combine columns, cells, and strings together.

Excel Concatenate

concatenate function in excel

To combine data in excel, you can use either merge cells, excel textjoin, or concatenate function. With excel concatenate; you can join various pieces together into a single cell. The syntax of concatenate function is

=Concatenate(text 1, text 2,…) where the text is the string or cell reference. You can see the result of using the concatenate function above.

If you want to separate the concatenated values, then use commas, space, or slashes. Here you can see the Excel concatenate formulas that have special characters. To concatenate cells, there are different ways that you can use this function to get the different result.

Let’s take an example,

=CONCATENATE(“abc”,”def”) will give you abcdef.

=CONCATENATE(“abc”,“ ”,”def”) gives you the outcome as abc def.

=CONCATENATE(A2&B2) gives an outcome as abcdef.

NOTE:

  • The excel concatenate function joins or concatenate up to 30 items together and give result in the form of text.
  • The main purpose of using the concatenate function is to join the text together.
  • The text item can be anything either text strings, cell references, or numbers.
  • You can use ampersand character in place of concatenate function. By using an ampersand, you can create a shorter formula and make the formula easier to read.

Difference between merge cell and excel concatenate function

Merging cells is basically merging two or more than two cells together in a single cell. After merging cell you will find a larger cell displayed in your worksheet.

Excel Concatenate

Suggested Read: How to merge cells in excel

3 Ways to combine strings, columns

In excel concatenate, you will find the combination of contents present in the cells. The concatenation function in excel joins two or more valuable content together. Check the screenshot below to understand the concept better.

1) How to Concatenate Data in Excel?

  • Take a list of items.
  • Choose a cell wherever you want to enter the concatenate formula.
  • Type “=CONCATENATE(text1, text2, text3,….)
  • Press enter.
  • Now drag the corner in other rows of the same column and see the result.

2) How to use Ampersand in Excel?

Ampersand is an operator that you can use to join two strings together. Here you can check the syntax of this operator

=Cell Reference 1&Cell Reference 2

Excel concatenate ampersand

What does ampersand (&) mean?

Many users think whether they use & or concatenate function. Both these functions work the same with only a few changes. These changes are

In Excel Concatenate function, there is a limit of 255 strings whereas there is no such limits of strings are there when you use ampersand sign. Apart from this, there is no difference between using two different concatenation methods. Many users find writing CONCATENATE function easier whereas many users use “&” ampersand sign.

3) Excel Text Join

With Excel text Join function, you can combine different text into a single text by specifying the delimiter. The syntax is,

=TEXTJOIN(delimiter, ignore_empty, text1, [text2],…..)

Where

Delimiter is separator where you can manually enter data or any cell reference consists of delimiter.

Ignore_Empty means if this value is true then it ignores all empty cells.

Text 1 is the text that you want to join.

Text 2 is another argument that can take 252 arguments that can be text strings or cell ranges.

Excel concatenate function

Suggested Read: Excel compare two columns

How to concatenate two columns in Excel?

To concatenate two columns, you need to enter a simple concatenation formula in the first cell. Then dragging the fill handle you will copy it down to the other cells. For example, you have two columns together that is column A and column B and you want to concatenate these two columns then enter the formula in the third column. After doing this, drag the fill handle to copy down the formula.

Leave a Reply

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