Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Combine Text from Two or More Cells into One Cell in Excel

When dealing with a large database in Excel, it may be necessary to combine texts from multiple cells into a single one. You can lose a lot of time and efficiency by retyping those texts manually. You can automatically combine text from different cells with Excel’s built-in formulas and features. So, you can breathe a sigh of relief. Today in this article, we will discuss 6 suitable ways to combine text from two or more cells into one cell in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.


6 Suitable Methods to Combine Text from Two or More Cells into One Cell in Excel

In the following section, we will use six effective and tricky methods to combine text from two or more cells into one cell in Excel. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference. In the following article, a dataset is given containing columns “First Name”, “Last Name”, “Age”, and “Country”. Then, we need to join all the text strings given in those columns and show them in the “Full Information” column. The overview of the dataset will look like this.sample data of Combining Text from Two or More Cells into One Cell in Excel


1. Insert CONCATENATE Function to Combine Text from Two or More Cells into One Cell

The CONCATENATE function is one of the most essential text functions that allows you to connect several text strings into one text string in a worksheet.  So, let’s walk through the following steps to do the task.

📌 Steps:

  • First, in cell F5, apply the CONCATENATE function. Insert the formula and the final form is:

=CONCATENATE(B5," ",C5,", ",D5,", ",E5)

Where,

B5, C5, D5, and E5 are “Ken”, “Adams”, “30”, and “Italy” serially. Moreover, these text strings are separated by using the commas (“, ”) in the function.

  • Next, press Enter to get the combined text.

Combine Text by using CONCATENATE function

  • Consequently, we got our first result using the CONCATENATE function.
  • Now move your mouse cursor to the bottom right corner of the formula cell and when the cursor shows the plus sign (+), double-click on the sign to apply the same function to the rest of the cells.
  • Hence, you’ll see the desired output as shown in the below picture.

 Text from Two or More Cells into One Cell in Excel

Read More: How to Concatenate Multiple Cells in Excel (7 Easy Ways)


2. Combine Text from Two or More Cells with Ampersand Symbol (&) in Excel

Using the Ampersand Symbol (&) you can easily join the text strings from multiple cells into one cell. We will use the same example that we used in the previous example. Therefore, learn the following steps to perform the task.

📌 Steps:

  • First of all, in cell F5 type the following formula:

=B5&” “&C5&”, “&D5&”, “&E5

The Ampersand symbol (&) connects the texts in the cell references, and the space (“  ”) and the comma (“, “) help to separate the texts.

  • After that, press Enter to get the result.

Combine Text from Two or More Cells into One Cell in Excel by using Ampersand symbol

  • Now apply the same formula to all the cells.

  • We have combined the required texts from the columns and returned them to a new column. Sometimes you need to start joining words from the next line.
  • To do it you can use the CHAR function.
  • Then the formula will be,

=B5&" "&C5&CHAR(10)&D5&", "&E5

Here, using the number 10 in the CHAR function will include a Line Breaker. So we will use CHAR(10).

use of CHAR function to Combine Text from Two or More Cells into One Cell in Excel

  • Afterward, press Enter and apply the same function to all the required cells.
  • From the screenshot, we can see that the line breaker is applied successfully.

Read More: How to Combine Date and Text in Excel (5 Ways)


3. Use CONCAT Function for Joining Text into One Cell

The CONCAT function does the same as the CONCATENATE function but its arguments are slightly different. So, follow the below steps to carry out the operation.

📌 Steps:

  • Firstly, in cell F5 of the previously used dataset, apply the CONCAT function. Insert the values and the final formula is:

=CONCAT(B5, C5,", ", D5,", ", E5)

Where, B5, C5, D5, and E5 are the cell references.

  • Then, press Enter to join those text strings.

use of CONCAT function to Combine Text from Two or More Cells into One Cell in Excel

  • Lastly, apply the same formula for all the cells to the end of the column.

Read More: How to Concatenate in Excel (5 Easy Methods)


Similar Readings


4. Merge Text from Two or More Cells Through Excel TEXTJOIN Function

The TEXTJOIN function in Excel joins text strings out of several cells and splits the combined data with whatever delimiter you specify. Let’s use this function to complete this task. Let’s walk through the following steps to do the task.

📌 Steps:

  • In the first cell of the Total Information column, apply the TEXTJOIN function. Insert the values into this function. The final form is:

=TEXTJOIN({" ",", ",", "},TRUE,B5:E5)

Where,

Delimiter is {” “,”, “,”, “}. The delimiter is a separator that will separate each text value that you combine. Here we used the space (” “) to separate the names and commas (“, “,”, “) to separate the other texts from the name.

Ignore_empty is TRUE because if there are any empty spaces, we want to ignore those.

Text1 is B5:E5. These are the text parts to be combined.

  • After that, press Enter to combine all the text as per our requirements.

using TEXTJOIN function to Combine Text from Two or More Cells into One Cell in Excel

  • Accordingly, we apply the same formula for all the cells in the column.

Read More: How to Merge Rows in Excel (5 Easy Ways)


5. Combine Text from Multiple Cells with Flash Fill Feature in Excel

The Flash Fill method is one of the easiest ways to combine texts. To accomplish the task, let’s walk through the below steps.

📌 Steps:

  • First, in the Full Information column write down the combined text to specify what the style will be for the rest of the cells.
  • Now, select the entire column, go to Home, click on Fill in the Editing Ribbon, and from Fill options, click on Flash Fill.

Combine Text from Multiple Cells with Flash Fill Feature in Excel

  • And the rest of the cells are filled with the combined texts instantly!

Read More: Combine Multiple Columns into One Column in Excel


6. Apply Excel Power Query for Combining Text into One Cell

In this method, we are going to demonstrate how to use Excel Power Query to combine text in one cell. Here we will use the Merge Column feature. Now, walk through the following process to do the task.

📌 Steps:

  • In the beginning, select any cell on the first table.
  • Then, go to the Data tab on the ribbon.
  • Next, select From Table/Range option from the Get & Transform Data group.

Using Power Query to Combine Text from Two or More Cells into One Cell in Excel

  • As a result, it will take the table into the power query.

  • Now, you have to select each column and then select Merge Columns from the Add Columns tab.

  • As a consequence, the Merge Columns dialog box will appear.
  • Subsequently, select Space in the Separator option.
  • After that, type your preferable name in the New column name( optional) option.
  • Finally, click on OK.

  • Therefore, you will get the following output where text strings are joined together in the Full Information column.

show the output

  • Consequently, select the Close & Load drop-down option from the Close group.

  • At last, you will get the following output.

getting final column

Read More: How to Combine Row into One Cell in Excel


Quick Notes

📌 The TEXTJOIN function is available in Excel for Office 365 and Excel 2019. Users from other versions of Excel won’t be able to use this function.

📌For the combined text to display in multiple lines, make sure the Wrap Text feature is turned on.


Conclusion

Today we discussed five different ways to combine your text from two or more cells into one cell. Using these methods, you can automatically combine the texts. This will save you from time-wasting and boredom. If you have any confusion or suggestion regarding this article, you are always welcome to comment and share. Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo