Combine 2 Cells in Excel: Best Ways to Nail it

If you are having trouble combining two cells in Excel and have important data scattered across, you can solve this problem with quick formulas. Because there are simple steps you can follow to merge text from several cells into one. You can do this with a few clicks and below you can read and apply 3 basic ways to do it.

How to combine 2 cells in excel: Combining Text from Two or More Cells into One Cell

First, let’s define what we mean by combining text from multiple cells. Essentially, this is about merging text from two or more cells. So this will be placing it all into one cell. You might want to do this for many reasons tough. Such as when you are working with a large spreadsheet and want to make it more organized. Or when you need to concatenate data from multiple cells into a single cell for use in a formula.

Even you have different reasons for combining text from multiple cells, you can use different solutions we stated below. Ok, now we can look at each of these methods and how to implement them in Microsoft Excel.

Method 1: Using the “&” Operator: how to combine to cells in excel

The easiest way to combine text from two or more cells into one cell is using “&” operator. The & operator can join two or more text strings into a single string.

1. Step 1: You will start by selecting the cell  to combine the text.
2. Step 2:  Then, you will type & operator in the cell to combine the text.
3. Step 3: So, you will click on the first cell you want to merge and then type & again.
4. Step 4: Now, you click on the second cell you want to merge and then type & again.
5. Step 5: You can do this to include all cells into your formula.
6. Step 6: And now, you will press Enter to combine the text.

Method 2: Using the CONCATENATE Function to join two cells content

The CONCATENATE function is another way to combin text in Excel. The CONCATENATE function joins two or more text strings into one string.

1. Step 1: You will start by selecting the cell to combine text from multiple cells into one cell.
2. Step 2: And secondly, you will type “=” to begin the formula.
3. Step 3: Now you will formulate “CONCATENATE(” in the cell for combined version.
4. Step 4: And you will click on the first cell you want to merge and then type “,”.
5. Step 5: Next step is clicking on the second cell to merge and then type “,”.
6. Step 6: With comma, you can add all cells you want.
7. Step 7: Now, you can Type “)” to close the formula and basically enter.

Tips to combine text from two or more cells into one cell

What happens if you have three columns with data in some cells but not in others? Of course, you could use another ampersand to add a cell to your formula. But every time you had a blank cell, your output would also have an extra space character. Here, you may use a formula using the TEXTJOIN function to get around this.

There are three parameters for the TEXTJOIN function.

• The delimiter is the first parameter. In your cells, this is the character or string of characters you wish to show up in between the text. We put “” because we want a gap between the names in our example.
• Ignoring empty cells is the second point. Depending on whether you want Excel to ignore blank cells, you may select true or false. We will type TRUE here because we do want.
• Text is the third parameter. The cells you wish to merge are these ones. You have the option to choose a single one or the full spectrum.
  Our whole formula is as follows: =TEXTJOIN(” “,TRUE,A2:C2)

Power Query Compatibility: Windows Excel 2010 or later

Another excellent method for combining several columns and adding a separating character is to use Power Query’s Merge Columns tool.

1. To begin combining cell contents in Power Query, you can go toe Data tab. And it is Power Query tab for previous Excel versions.
2. Depending on your version, you should select the option that reads From Sheet, From Table or From Range. This will cause the Power Query Editor to display your data. Here, you can choose whatever columns you wish to merge.
3. Next, on the Add Column menu, you can choose Merge Columns. This will open the window for merging columns.
4. Also, you can decide how you would want the text in each column to be divided. We like to have a gap between the names in our situation.
5. From this box, you can also give the column a name and then press OK for Window for Merging Columns
6. The preview will be updated to include a new column with the combined content.

Conclusion: combine text from multiple cells into one cell

Combining text from two or more cells into one  is a common task when working with different files and spreadsheets. This article here mentioned three methods to achieve join two cells content. Such as the concatenate function, the ampersand operator and the text join function. Each method has its advantages and disadvantages. But as you practice, you can find the method for you need. And it will depend on your specific requirements.

Kathy Castle

A dedicated Career Coach, Agile Trainer and certified Senior Portfolio and Project Management Professional and writer holding a bachelor’s degree in Structural Engineering and over 20 years of professional experience in Professional Development / Career Coaching, Portfolio/Program/Project Management, Construction Management, and Business Development. She is the Content Manager of ProjectCubicle.