Excel Formulas CONCATENATE Bring The Text Of Multiple Cells Into One Cell With Paste Special

CONCATENATE Excel Formula A simple formula to bring the text of multiple cells into a single cell

Usage of Excel Formula CONCATENATE

If any of our information is in 2 or more cells separately, then we can put all the text in one cell just by applying the CONCATENATE Excel formula. Suppose we have a list in which first name is in one column and last name is in second column.So we will apply this formula in the cell of the next column and can show the first and last name in the same cell. Between the two names, we can place a space or any special character, Such as dash or star.

How to Apply CONCATENATE Excel formula ?

1) Join 2 Cells –

In the List below, the names in 3 different columns B, C, D are middle characters and last names. Apply this formula in the next cell. In cell F5, press the sign of = type CONCATENATE and open the bracket.

We will see the highlights of the formula Text1, text2,. Click on the first name cell C5 and press comma, then click on cell C6. We will take only two cells here. Close the bracket. You can add up to 255 cells if you want. Finally press enter.

Texts of both the cells have started appearing in one cell.You can see the formula in the formula bar, and the Joins text in the cell.

3. Join Including Space

Now we have to give space between two text. After clicking the first cell, give a space in the double inverted comma by giving a comma.

=CONCATENATE(C6,” “,E6)

3. Join Multiple Cell Including Space

Similarly, we can add multiple text …

=CONCATENATE(C7,” “,D7,” “,E7)

4. Join Cell including Special Characters

We can also put a special character in the middle of two text. Just like we have given space in double inverted comma, similarly we have to put special character in double inverted comma.

=CONCATENATE(C8,”-“,E8) or =CONCATENATE(C8,”@”,E8)

“*” “#” “@” “$” “/”

We can also take a space special character from a different cell. In this, we only have to click the cell with a comma in the middle.

=CONCATENATE(C8,D8,E8)

5. Join numbers (Ex.7)

This formula also adds different numbers.

Excel Function Paste Special

Just paste the value or text out of the cell’s formula

Information derived from any formula is only visible to us. If you copy the cell of the formula, then only the formula is copied. If we want to paste the text or value in different place or in the same place, then paste special can use this excel function.
Copy all the cells in the formula.

Click on the first cell wherever you want to paste. Right click the mouse. Click on the 123 is symbol of the paste value.

Or after right clicking, a new window will open by clicking on Paste Special.

clicking on the values and clicking on OK.

The formula will be removed and only the text or value will be pasted.

Excel Formulas CONCATENATE Bring the text of multiple cells into one cell with Paste Special