Excel CONCATENATE Function

The ‘CONCATENATE’ function in Excel will amalgamate a series of selected cells into one text string.

This formula can be useful in a number of scenarios. For instance, you may have first name and last name data which is entered into separate columns. You can use concatenate to merge that data (i.e. creating a full name column).

An example of a more advanced use would be using concatenate to create unique row identifiers when dealing with large data sets. For example, you may have a list of tens of thousands of product sales. You could use concatenate to create ‘Market’ + ‘Customer’ + ‘Product’ (‘MCP’) combinations. Once each row has an MCP identifier, you could use the SUMIF function to calculate the product sales attributable to each unique MCP combination.

Note that the CONCATENATE function can join up to a maximum of 30 text items/cells together. All cells can be subjected to the CONCATENATE function, though bear in mind that all cells, including numbers, will be transformed into ‘text’.

Syntax

=CONCATENATE(text1,[text2],[test3])

Arguments

  • Text1 – the first input cell that you wish to amalgamate with other cells/data
  • Text2 – the second input cell
  • etc

Most typically, these inputs (i.e. text1, text2) refer to cell references (e.g. A1, A2). However, this does not have to be the case. Text can be manually entered within the formula (within “”), or specific text can be combined with cell references within the same formula. For example, if you intend to use CONCATENATE to combine first and last names such as Joe + Bloggs, then the concatenate formula =CONCATENATE(“Joe”,”Bloggs”) would result in ‘JoeBloggs’ with no space. Assuming Joe is in cell A1, and Bloggs in cell A2, the correct formula to deliver the output ‘Joe Bloggs’ would be =CONCATENATE(A1,” “,A2) or =CONCATENATE(“Joe”,” “,”Bloggs”).

Function category

Text

Returns

The CONCATENATE function joins multiple text items into one text string.

Download Excel File

 Example of CONCATENATE function

Comments (No)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.