How do you combine cells with text value?

While dealing with a large database in Excel you may have to combine texts from two or more cells into one cell. Retyping those texts manually can cost you a great deal of time and decrease your efficiency. Excel has some built-in formulas and features which will help you to combine text from different cells automatically. So, you can breathe a sigh of relief. Today in this article, we will discuss some ways to combine text from two or more cells into one cell in Excel.

Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

Combine Text from Two or More Cells into One Cell (5 Methods)

If you have different parts of a text string in different cells that you want to combine, then you can easily do this task using the method discussed below.

1. Using the CONCATENATE Function

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.

Step-1:

In the following example, a dataset is given containing columns the “First Name”, “Last Name”, “Age”, “Country”. We need to join all the text strings given in those columns and show them in the “Full Information” column.

Step-2:

In cell F4, Apply the CONCATENATE function. Insert the formula and the final form is

=CONCATENATE(B4,” “,C4,”, “,D4,”, “,E4)

Where,

B4, C4, D4, E4 is “Ken”, “Adams”, “30”, “Italy” serially. These text strings are separated by using the commas (“, ”) in the function.

Press “Enter” to get the combined text.

Step-3:

We have 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 plus sign (+), double click on the sign to apply the same function to the rest of the cells.

Read More:Combine Multiple Columns into One Column in Excel

2. Combine Text with the Ampersand Symbol (&)

Using the Ampersand Symbol (&) you can easily join the text strings from multiple cells into one cell.

Step-1:

We will use the same example that we used in the previous example. Now in cell F4 type this formula,

=B4&” “&C4&”, “&D4&”, “&E4

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

Press “Enter” to get the result.

Step-2:

Now apply the same formula to all the cells.

Step-3:

We have combined the required texts from the columns and returned them into 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,

=B4&” “&C4&CHAR(10)&D4&”, “&E4

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

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.

Related Content: Combine Multiple Cells Into One Cell Separated By Comma In Excel

3. Using the CONCAT Function

The CONCAT function does the same as the CONCATENATE function but its arguments are slightly different.

Step-1:

In cell F4 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, E5 are the cell references.

Press “Enter” to join those text strings.

Step-2:

Now apply the same formula for all the cells to the end of the column.

4. Using the TEXTJOIN Function

The TEXTJOIN function in Excel merges text strings from multiple cells and separates the combined values with any delimiter that you mention. Let’s use this function to complete this task.

Step-1:

In the first cell of the Total Information column, apply the TEXTJOIN function. The generic TEXTJOIN function is,

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Insert the values into this function. The final form is,

=TEXTJOIN({” “,”, “,”, “},TRUE,B4:E4)

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 B4:E4. These are the text parts to be combined.

Now Press “Enter” to combine all the text as per our requirements.

Step-2:

Apply the same formula for all the cells in the column.

5. Combine Text the FLASH FILL Feature

The FLASH FILL method is one of the easiest ways to combine texts. Let’s learn.

Step-1:

First, in the Full Information column write down the combined text to specify what the style will be for the rest of the cells.

Step-2:

Now select the entire column, go to Home, click on Fill in the Editing Ribbon, and from Fill options, click on Flash Fill.

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

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.

Further Readings

• How to Concatenate Two Columns In Excel (5 Methods)
• Concatenate Numbers in Excel (4 Quick Formulas)
• How to Combine Date and Text in Excel (5 Ways)
• Concatenate Date and Time in Excel (4 Formulas)
• Opposite of Concatenate in Excel (4 Options)
• How to Add a 1 in Front of Numbers in Excel [7 Special Tricks]