These two were the methods we found to be the simplest and most applicable to all. There are plenty of other ways in Excel, like using VB script, or the TEXTJOIN function (available in newer versions and Office 365).
![formula for merging cells in excel that both have data formula for merging cells in excel that both have data](https://cdn.extendoffice.com/images/stories/doc-excel/merge-cells/doc-merge-columns-into-one-cell-1.png)
It’s important to note though, that these are not the only two ways to do this. First, this video shows a simple formula to combine text and numbers in Excel. If you want the numbers formatted a certain way, use the TEXT function to set that up. Fortunately, Excel 2016 (and all prior versions) make it very easy to grab data from many different cells and put it into one. In this tutorial, we showed you two ways in which you can combine first and last names into a single column in Excel. In all versions of Excel, you can use a simple formula, with the & operator, to combine values from different cells. So, even if you delete the original two columns containing first and last names respectively, the combined column remains unaffected. Since this method doesn’t involve a formula, it doesn’t depend on the original columns. In the same way, you can also set the pattern to have the last name followed by a comma, followed by the first name. Formula Reference in Excel 2010, Most formulas you create include references to cells or ranges. Finally, we concatenated the value in C2 (the last name). We followed this by a dot operator and space. Here, we specified that if cell B2 is not blank (that means if a middle name exists), we want just the first character from the cell B2. The LEFT() function takes the specified number of characters starting from the left side of the text in a cell. If you want to have the first name, followed by the middle name’s initial, followed by the last name, then your formula in D2 can be =CONCATENATE(A2,IF(B2=””,” “,” “&LEFT(B2,1)&”.”),C2)., even though it is highly recommended that you don’t merge them unless you absolutely need to.
![formula for merging cells in excel that both have data formula for merging cells in excel that both have data](https://cdn.educba.com/academy/wp-content/uploads/2019/01/Unmerging-the-Cells-in-Excel-feature-image.png)
Formula for merging cells in excel that both have data how to#
If you have a first name, last name, and a middle name in cells A2, B2, and C2 respectively, your formula in D2 can be =CONCATENATE(A2,” “,B2,” “,C2). This guide will show you how to merge cells in Excel Excel Resources Learn Excel online with 100's of free Excel tutorials, resources, guides & cheat sheets CFI's resources are the best way to learn Excel on your own terms.You can replace the separating character with any character you need.So for this example, C3 would have the formula: =CONCATENATE(B2,”, “,A2) To combine the data from cells with the CONCAT formula in Excel, follow these steps: Type CONCAT ( to begin your formula Type the address of the first cell that you want to combine with, such as A2 Type a comma, and then type the address of the next cell that you want to combine with, such as B2 Press enter on the keyboard. Also, replace the first cell reference with the second cell reference and vice-versa. If you want to put the last name followed by a comma, followed by the first name, then instead of a space, put a comma within quotes.