If you want to merge text data in different columns in Excel, how do you go about it? That’s easy.
If you have to copy data from other programs into Excel, it does not usually copy the data in the way we want it. Excel will arrange the data in separate columns. But you’ll realize that some of the text data need to be merged together in one column.
Let’s use this example above.
Imagine you have just received a list of client’s contact information in a word file or any other program and you want to copy the information into excel to enable you properly analyze your customer’s information. Each of information will be arranged into separate rows and columns. However, you want the customer’s Last name and First name to appear together in the same cell, that is, in only one column.
If you do this manually, you may end up spending your whole day just to organize these details and I’m sure no one will be cut out for that.
So, here is how to merge text in different columns in Excel.
Interestingly, there are two quick and easy ways to merge text in Excel.
- By Using the CONCATENATE function
2. By using the Ampersand (&) symbol.
Excel’s CONCATENATE function allows you to merge texts from multiple cells into just one cell.
To do this:
- Create a new column for the merged data – Column C
2. Select the first cell to be merged – Cell C2
3. Type this formula into the cell: =Concatenate(A2,“ ”,B2)
Select the first cell, insert comma (,) to separate the cells and insert quotes (“ ”) for spacing, then select the second cell and close the bracket.
Take note of the formula in the formula bar.
4. Press Enter
Now, you can see that the new column created contains both the Last name and First name. If you want the First name to appear first, before the last name, all you have to do is simply select the First name cell before the Last name in the formula.
You will have to adjust the formula to =Concatenate(B2, “ ”,A2) instead in the formula bar.
5. Then click on the first combined cell to drag down and fill the rest of the cells.
Now you have all the customer’s names merged in one column just as you want it.
2. AMPERSAND SYMBOL
Another way to merge texts of multiple cells is by using a simple trick using the Ampersand symbol – (&). If the word ‘Concatenate’ seems too long for you to type, then you can jump on this trick.
To do this:
- Create a new column for the merged data.
2. Select the first cell to be merged and type in this formula:
=A2& “ ”&B2
Did you notice the formula in the cell and formula bar?
3. Press Enter
Again, you can see that cells A2 and B2 have been merged into cell C2.
4. Now drag the first cell to fill in the other merged cells.
Once again, you have the other names merged into their cells by simply dragging the formula in the first cell to fill in the other cells.
The good thing about this is that you can do this for a list of over a hundred thousand names and you will be done in no time.
I hope you found this tips useful. Don’t forget to share your thoughts in the comment section. You may also like to share.