How to Concatenate in Excel

Jul 5, 2020 ยท 2 minute read

Sometimes you have different columns and you want to combine their data of the cells together.

For that, we want to explain two methods

How to Concatenate in Excel

First method;

You can use “Concatenate” formula to bring the data of those columns together.

For that you can follow these steps;

1- Write this formula

=Concatenate(B3,C3,D3)

If you want to have space between the data of each column, you can add space between data by this formula;

=Concatenate(B3," “,C3,” “,D3)

Here B3 and C3 and D3 are the locations of the cells in the sheet.

concatenate

2- Specify this formula to other cells of the column.

concatenate formula

Second Method

For combining the data of the cell you can use this formula too.

=B3&C3&D3

Here B3 and C3 and D3 are the locations of the cells in the sheet.

If you want to have space between the data of cells in the columns you can use this formula

=B3&” “&C3&” “&D3

connect

How Can I Add a Specific Word to All Cells of the Column;

Sometimes you have a lot of data in one column and you want to add a specific word at the beginning of the cells or at the end of the cells.

For that you can use these formulae;

Add Prefix;

If you want to add a prefix to the data which you have in the column you can use these methods;

you can add a prefix to the value of the cells in one column with this formula;

In this question, we added the word “Usa” in front of all digits of the cells column D.

=Concatenate(“Usa”,D3)

In this formula, “D3” is the location of the cell which we want to add a prefix for that.

concatenate prefix 1

You can also ask excel add the value of the specific cell in front of your data in a specific column.

=Concatenate($C$2,D3)

As you see we fixed the value of the cell “C2”, due to when you specified this formula to other cells your formula work correctly.

concatenate prefix

You can also use this formula to add a specific prefix to your data.

=$C$2&D3

prefix&

In these formulae $C$2 is the location of the cell which you wrote your prefix there.

“D3” is the location of your data in column C.

Add Suffix;

you can also add a suffix to your data, here we are going to explain 3 methods for that;

=Concatenate(D3,“Usa”)

suffix1

=Concatenate(D3,$C$2)

suffix2

=D3&$C$2

suffix3

In these formulae $C$2 is the location of the cell which you wrote your prefix there.

“D3” is the location of your data in column D.

excel