Nov 10, 2016

How to Concatenate Text from Multiple Cells into One Cell in MS Excel

Combine Text From Multipe Cells into One Cell

If you are working in a large worksheet in an MS Excel workbook in which you need to combine text from multiple cells into one cell, then you are reading a correct article because you don’t have to retype all that text. In this guide, we'll walk you through the steps to combine text from multiple cells into one cell in MS Excel.






Concatenation is easy method to combine text from multiple cells into one and there is a special CONCATENATE function in Excel to accomplish this. This function allows you to combine text from different cells into one cell. For example, we have a worksheet containing names and contact information. We want to combine the Last Name and First Name columns in each row into the Full Name column.
To start, select the first cell that will contain the combined, or concatenated, text. Start typing the function into the cell, starting with an equals sign, as shown in image below.

=CONCATENATE(



Lets enter the arguments for the CONCATENATE function, which instruct the function which cells to combine. I want to combine the first two columns, with the First Name (column B) first and then the Last Name (column A). So, our two arguments for the function will be B2 and A2.

There are two methods, you can enter the arguments. First, you can type the cell references, separated by commas, after the opening parenthesis and then add a closing parenthesis at the end:

=CONCATENATE(B2,A2)
 
We can also click on a cell to enter it into the CONCATENATE function. In my example, after typing the name of the function and the opening parenthesis, we click on the B2 cell, type a comma after B2 in the function, click on the A2 cell, and then type the closing parenthesis after A2 in the function.

Hit Enter when you’re done adding the cell references to the function.



Did you notice that there is no space in between the first and last name? That’s because the CONCATENATE function combines exactly what’s in the arguments you give it and nothing more. There is no space after the first name in B2, so no space was added. If you want to add a space, or any other punctuation or details, you must instruct the CONCATENATE function to include it.


To add a space between the first and last names, we add a space as another argument to the function, in between the cell references. To accomplish this, type a space surrounded by double quotes. Make sure the three arguments are separated by commas.

=CONCATENATE(B2," ",A2)
 
Hit Enter.


Excellent. Now, there is a space between the first and last names.


You’re probably thinking you have to type that function in every cell in the column or manually copy it to each cell in the column. Actually, you don’t. We’ve got another neat trick that will help you quickly copy the CONCATENATE function to the other cells in the column (or row). 

Select the cell in which you just entered the CONCATENATE function. The small square on the lower-right corner of the selected is called the fill handle. The fill handle allows you to quickly copy and paste content to adjacent cells in the same row or column. 


Move your cursor over the fill handle until it turns into a black plus sign and then click and drag it down.







The function you just entered is copied down to the rest of the cells in that column, and the cell references are changed to match the row number for each row.



We can also concatenate text from multiple cells using the ampersand (&) operator. For example, we can enter =B2&" "&A2 to get the same result as =CONCATENATE(B2,” “,A2) . There’s no real advantage of using one over the other. although using the ampersand operator results in a shorter entry. However, the CONCATENATE function may be more readable, making it easier to understand what’s happening in the cell.

Post a Comment

 
TECH SUPPORT © 2012 - Designed by INFOSBIRD