Home

Excel

How To Combine Text In Excel?

April 25, 2024
By Vishnu Vardhan

Have you ever found yourself needing to combine information from multiple cells in your Excel spreadsheet? Perhaps you’re working with customer names that are separated across first and last name columns, or maybe you want to create unique identifiers by combining different data points. Whatever the scenario, efficiently combining text in Excel can save you time and effort. This guide will walk you through various methods for achieving this task, ensuring your data is organized and presented in a clear and concise manner. Let’s check out the methods Excel offers for merging text smoothly.

Outline

How To Combine Text From Two Or More Cells Into One

Excel provides several methods to accomplish this, allowing you to concatenate text quickly and efficiently. Let’s explore the different ways to combine text from two or more cells into one cell.

1. Combine Text Using Ampersand Symbol (&)

To combine text in Excel, you can use the ampersand (&) symbol. This symbol joins text from different cells into one. Here’s how:

Select the cell where you want the combined text to appear.
Type an equal sign (=) to indicate you’re entering a formula.
Select the first cell containing the text you want to combine.
Add the ampersand symbol (&) to join the text.
Enclose a separator (like a space) in double quotes if you want to add something between the combined texts.
Select the second cell containing the text you want to combine.

Press Enter.

For instance, suppose you have first names in column A and last names in column B, and you aim to merge them into full names in column C. Utilize the formula =A2 & ” ” & B2 in cell C2. This formula combines the content of cell A2, a space, and the content of cell B2 into cell C2.

This method isn’t limited to referencing cells; it can also be employed to merge text strings to create more meaningful results. For example: = A3&” [Text]”&B3.

2. Combine Text Using the CONCAT Function

Another way to combine text from different cells into one cell is by using the CONCAT function. This function works similarly to the ampersand (&) symbol but offers a more structured approach.

Formula:

=CONCAT(text1, [text2], …)

Where:

text1: The first text you want to combine, can be a cell reference (e.g., A1) or a text string (“Hello”).

[text2], …: Additional text arguments (text2, text3, etc.) separated by commas to concatenate multiple text elements, each can be a cell reference or text string.

Choose where you want the combined text to appear.
Begin with an equal sign (=) in the selected cell.
Enter CONCAT(“ followed by an opening parenthesis.
Inside the parentheses, list the cell references or text strings you want to combine, separated by commas.
End the formula with a closing parenthesis.

Press Enter to combine the specified text into the chosen cell.

3. Combine Text Using the TEXTJOIN Function

If you’re using Excel 2019 or newer, TEXTJOIN is your friend. It’s great for combining text from different cells into one. It’s more versatile than using just the ampersand (&) or CONCAT.

If you’re working with several columns where some cells might be empty, just adding an ampersand in your formula can create extra spaces in the result. To prevent this, try TEXTJOIN instead. Here’s how it works:

Formula:

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

Where:

Delimiter: This is the separator you want to use between the combined text elements. It can be a text string (like “, “) or a cell reference.

Ignore_empty (TRUE/FALSE): This decides how empty cells are handled. TRUE (default) omits empty cells from the output. FALSE includes them, separated by the delimiter.

Text1: This is the first piece of text you want to combine. It can be a cell reference, or a text string.

[Text2], …: You can add more text elements to concatenate. Each one can be a cell reference, a range of cells, or a text string, separated by commas.

Choose the cell where you desire the combined text to appear.
Enter an equal sign (=) in the selected cell, indicating the start of a formula.
Type TEXTJOIN( followed by an opening parenthesis.
Specify the delimiter text in quotes (e.g., “, “) or enter a cell reference containing the delimiter. Separate this with a comma.
If you want to exclude empty cells, enter TRUE. Otherwise, enter FALSE. Separate this with a comma.
Click on the cell containing the first text, or select the range of cells you want to combine. Separate subsequent text elements (cell references, ranges, or text strings in quotes) using commas.
Enter a closing parenthesis ) to finalize the formula.

Press Enter to execute the formula. The text elements will be combined within the destination cell, separated by the specified delimiter.

4. Combine Text Using Power Query

To combine cell contents using Power Query, follow these steps:

Go to the Data tab (or Power Query tab for older Excel versions).
Choose “From Sheet” or “From Table or Range” to preview your data in the Power Query Editor.

Select the columns you want to combine.
Click “Merge Columns” in the Add Column tab to open the Merge Columns Window.

Choose how you want to separate the text from each column (e.g., with a space).
Optionally, name the new column.
Click OK.

A new column with the merged text will be added to the preview.

Power Query automatically handles and skips blank cells when combining columns.
If the preview looks good, click on the top half of the “Close & Load” button to export the merged data into your worksheet.

How To Combine A Range Of Cells In Excel?

When you’re working with just a few cells, typing references isn’t a hassle. But for a big range, typing each one by hand takes time and mistakes happen. Luckily, there are smart ways to merge text from lots of cells smoothly. Let’s check out these methods to make handling your data easier.

By Using TRANSPOSE Function

When dealing with a large number of cells in Excel, manually typing each cell reference can be time-consuming. You can use the TRANSPOSE function to quickly merge a range of values into one.

Here’s how:

Enter the TRANSPOSE formula in the cell where you want the result, like this: =TRANSPOSE(A2:A14).

Press F9 in the formula bar to convert the formula into an array of values.
Remove the curly braces around the array.
Type =CONCAT( before the first value, and add a closing parenthesis after the last value. Press Enter.

By Using CONCAT Function

In Excel 365 and Excel 2021, you can merge the contents of several cells into one using the CONCAT function. Here’s how:

Select the cell where you want the combined output to appear.
Click on the formula bar of the chosen cell.
Type =CONCAT(, followed by an opening parenthesis.
Enter the cell range references of the data you want to combine, separated by commas.
For example, to combine the contents of ranges A1:A5 and B1:B5, use the formula =CONCAT(A1:A5, B1:B5).

Difference Between Concatenating And Merge Cells?

Concatenating Cells: Concatenation merges the contents of multiple cells into one cell without changing the originals. This helps join text or values from different cells. For instance, you can combine first and last names from separate cells to create a full name. Concatenation is typically done using formulas like CONCATENATE, CONCAT, or the ampersand (&) operator.

Merging Cells: Merging cells combine multiple adjacent cells into one larger cell. When cells are merged, the content of the upper-left cell is kept, and other cell contents are deleted. Merging cells is often used for formatting, like creating a header that spans several columns or rows. However, merging cells should be used carefully, as it can alter the structure of your data and make it challenging to work with in some cases. In addition to Excel, you can merge cells in Google Sheets as well.

In summary, concatenating cells combine their contents into one cell without changing the original cells, while merging cells combines multiple cells into one larger cell, changing the worksheet’s structure.

Also check:

Tips For Combining Text in Excel

Simple Text Combination: For basic text merging use the ampersand (&) symbol.
More Options: For control over separators, empty cells, or formatting, try the CONCAT or TEXTJOIN functions.
Separators: Use spaces or commas between combined elements for readability.
Consistent Formatting: Keep formatting (like capitalization) consistent for a professional appearance.
TEXTJOIN: In Excel 2019+, use TEXTJOIN with the ignore_empty argument to handle empty cells.
Conditional Formatting: Consider conditional formatting for more control over formatting based on the combined content.

Troubleshooting Tips For Combining Text In Excel

Check for Data Errors: Ensure that the data you’re combining does not contain any errors, such as misspellings or incorrect formats, which could affect the outcome of your combined text.
Verify Cell References: Double-check that your cell references in the concatenation formula are accurate and pointing to the correct cells. Incorrect references can lead to unexpected results or errors in the combined text.
Handle Special Characters: If your text contains special characters, such as commas or quotation marks, make sure to properly handle them within your concatenation formula by using escape characters or enclosing the text in double-quotes.
Check for Trailing Spaces: Sometimes, cells may contain trailing spaces that are not visible. Use the TRIM function to remove any leading or trailing spaces from the text before combining them to avoid unintended spaces in your combined text.
Debug Your Formula: If your concatenation formula is not producing the expected result, break it down into smaller parts and evaluate each part individually to identify any errors or inconsistencies.
Consider Data Types: Ensure that the data types of the cells you’re combining are compatible. Mixing text and numeric values in a concatenation formula may result in errors or unexpected outcomes.
Use Error Handling Functions: Implement error handling functions, such as IFERROR or ISERROR, to catch and handle any errors that may occur during the text-combining process.

FAQs:

1. How much text can I combine in Excel?

Excel has a limit on the length of text that can be entered into a cell (about 32,767 characters). If you exceed this limit, you may encounter errors or truncated text.

2. How do I handle empty cells when combining text in Excel?

You can use the IF function to check if a cell is empty and only include the text if the cell is not empty. For example, =IF(A1<>””,A1,””) will only include the text from cell A1 if it is not empty.

3. Can I combine text with a line break in Excel?

Yes, you can include a line break in your combined text by using the CHAR(10) function, which represents a line break. For example, =A1&CHAR(10)&B1 will combine the text from cells A1 and B1 with a line break between them.

4. Can I combine text from cells in different worksheets or workbooks?

Yes, you can. Simply reference the cells from the other worksheet or workbook in your CONCATENATE function or “&” operator as you would with cells in the same worksheet.

5. What if some of the cells I want to combine contain numbers?

Excel will automatically convert numbers to text when you concatenate them with other text. However, you can also use the TEXT function to format numbers as text with specific formatting before concatenating them.

Conclusion

Now that you’ve learned the techniques in this guide, you can easily combine text in your Excel sheets. Just pick the method that fits your needs: use the simple ampersand (&) for basic combining or the advanced TEXTJOIN function for dealing with empty cells and special formatting. So, go ahead and tackle your data tasks confidently with these handy text merging skills! Hope this guide made things clearer for you.