5 ways to use Concatenate in Excel

The data you work on can either be organized or unorganized. We often clean, structure, and manage the unorganized data using various tools and functions of excel. One of the types of data structuring consists of splitting the content of one cell into multiple cells or vice versa, i.e., combining cell values of different cells into one cell. That is where we require the concatenate function.

Here are 5 ways to use the concatenate function in excel:

5 ways to use Concatenate in Excel

• How to concatenate different text in excel

Concatenate is used if only the content of the cells is to be combined. In short, it is the process of joining values that reside in different cells.

Syntax for Concatenate: =CONCATENATE(text1,text2,….textN)

Let us suppose text 1 is Jim and text 2 is Jam. The concatenate function to combine them will be =Concatenate(“Jim”,”Jam”), and the output will be JimJam. CH

• How to Concatenate in Excel using different symbols

You must note that every text needs to be placed within the quotation mark, i.e., “ “ , if you wish to put space between two texts, then the quotation mark is given a blank space. This blank space can be replaced by any other sign, like a comma, a hyphen, or even an alphabet (as required).

For example, to separate JimJam with a hyphen, the function will be =Concatenate(“Jim”,”-”,”Jam”), and the output will be Jim-Jam.

A quotation mark is not used when a cell reference or a formula is mentioned.

• How to Concatenate in Excel using Line Breaks

Line breaks become essential when a lot of data is to be entered into a single sentence. To add line breaks in concatenate function, use the code CHAR(10). Then, putting the code in the concatenate function will automatically place line breaks.

For example, to insert line breaks in JimJam, the function will be =Concatenate(“Jim”,Char(10),”Jam”), and the output will be Jim Jam in a single cell but each in a separate line.

Char(10) is a code; thus, we do not use quotation marks.

• How to Concatenate an entire Column in Excel

While concatenating an entire column is a 2 step process,

Firstly, Place the formula for concatenation in one cell.

Suppose you want to combine the texts in Column A & Column B in Column C, so the function for row 1 of column C will be =CONCATENATE(A1,” “,B1).

The next step involves dragging down the formula to the entire column, and you are done with concatenating two different columns.

• How to Concatenate a range of cells in Excel

A range of cells can not be concatenated using an array of cells. It requires different cell references for each value.

Different cell references can be concatenated by manually writing the cell reference or by pressing CTRL and selecting the cell reference.

Concatenate in Excel is one of the most valuable and essential functions in data cleaning. It is often used to generate unique Identification pins and codes. It can be applied through various methods, as discussed above. Tell us in the comment section which function worked for you the most.