What Is Combine Text From Two Or More Cells Into One Cell?
The combine text from two or more cells into one cell option concatenates texts specified in multiple cells into a single cell. And the text values to integrate can be in the same worksheet as the target cell or different spreadsheets and workbooks.
Users can combine text from two or more cells into one cell to arrange and format data spread across multiple cells into a cell based on a specific pattern or logic.
For example, the table below shows the employee details in cells A2:C2.
And the requirement is to create the employee ID in cell D2 using the given data, with an underscore between each text value.
Then, based on the combine text from two or more cells into one celldefinition, we can use the ‘&’ symbol to concatenate the given texts in the specified format in the target cell.
In the above example, we use the Excel cell references to the required text values to create the concatenation formula. And as the ‘_’ symbol is a special character, we enter it in double quotes in the formula.
Further, the cell references and ‘_’ symbol are arranged in the formula according to the specified requirement. And the ‘&’ symbol combines them in the same order to give the employee ID in the desired format as the output.
Also, we can enter the texts directly in the formula instead of the cell references to the text values to achieve the required output.
Please note that cell C2 contains an Ampersand symbol, which we may think will result in erroneous output. But when we supply a reference to the cell C2 value or enter it directly in the formula in double quotes, the formula will consider it as a text to concatenate.
Table of contents
Key Takeaways
- The combine text from two or more cells into one cell in Excel is an option that enables one to merge multiple cell texts and display the concatenated text in a single cell.
- Users can concatenate multiple cell texts in a spreadsheet to logically arrange massive chunks of data in fewer cells.
- We can use the ‘&’ symbol to concatenate text values from multiple cells across one or more sheets into one cell in a spreadsheet.
- We can provide cell values and references as inputs to the ‘&’ symbol-based text concatenation formula. Also, we can manipulate cell text values and combine them with texts, constants, and special characters according to our text concatenation requirements.
How To Combine Text From Two Or More Cells Into One Cell?
The steps to combine text from two or more cells into one cell are as follows:
- Choose the target cell to show the outcome.
- Enter the ‘=’ sign, the first cell value or reference to the cell containing the first value, and the ‘&’ symbol. And then, enter the remaining cell values or cell references to the values and required texts and characters, with the ‘&’ symbol specified between each value.
- Press Enter to view the combine text from two or more cells into one cell formula output in the target cell.
Please note that the ‘&’ symbol does not combine texts from multiple cells into a single cell with delimiters. Thus, we must specify the delimiters such as a space character, comma, and full stop in the formula at the positions where they must appear in the final concatenated data.
Examples
Check out the following examples that explain the different methods to combine text from two or more cells into one cell to use the option effectively.
Example #1 – Using The Ampersand (&) Symbol
The table below contains US tech companies’ headquarters details, with each company’s address across multiple cells in each row of columns D to H.
And the requirement is to display each company’s headquarters location as a postal address in column I.
Then, based on the combine text from two or more cells into one cell concept, we can use the Ampersand symbol-based formula in each target cell. The formula will merge the cell values in each row in the required order to give the concatenated text in the desired postal address format.
- Step 1: Choose cell I3, enter the ‘=’ sign, cell reference C3 and the ‘&’ symbol. The specified cell reference will show the company name in the first line of the resulting address.
Next, enter CHAR(10), where 10 is the ASCII code for the line feed, indicating the end-of-line. And we use it to insert a line break since an address must start from the next line below the company name.
And then, enter the ‘&’ symbol.
Next, enter the cell reference D3 to show the street address in the new line below the company name and then the ‘&’ symbol.
Again, enter the CHAR(10) function to insert a line break since the address must show only the street name below the company name. And enter the ‘&’ symbol.
Next, supply the cell reference E3 to display the city name in the new line. And then, enter the ‘&’ symbol.
We must show the city, state, and ZIP Code in the same line, with the city and state separated with a comma.
So, enter the comma and a space character within double quotes and enter the ‘&’ symbol. While the comma will appear in the output, the space character ensures one space distance between the comma and the state name in the output.
Next, supply the cell reference F3 to display the state in the output and type the ‘&’ symbol.
And then, enter a space character within double quotes to ensure one space distance between the state name and the ZIP Code. And enter the ‘&’ symbol.
Next, enter the reference to cell G3 value to display the ZIP Code, followed by the ‘&’ symbol.
And then, as we must display the country in a new line, enter CHAR(10) to insert a line break, followed by the ‘&’ symbol.
Finally, enter the reference to the cell H3 value to display the country in the last line of the required address.
So, the formula will be:
=C3&CHAR(10)&D3&CHAR(10)&E3&”, “&F3&” “&G3&CHAR(10)&H3
- Step 2: Press Enter to execute the above formula.
- Step 3: Copy the formula in cells I4:I7 using the Excel fill handle option.
However, we cannot see the Excel line breaks in the output. So, we will use the Excel Wrap Text option to view the outcome in the desired format.
- Step 4: Select cell I3:I7 and click Home → Wrap Text or use the Excel keyboard shortcut Alt + H + W to wrap texts in the chosen cells.
Thus, the results in column I appear in the required postal address format.
While the company name appears in the first line, the street name follows in the next line due to the inserted line break.
Likewise, due to the inserted line feed function, the city and state names appear in the next line with a comma in between and the ZIP Code in the same line.
And finally, with the CHAR(10) supplied before the reference to the cell H3 value, the country name gets displayed in the last line.
Example #2 – Combine Cell Reference Values And Manual Values
The table below shows the order dates and delivery status details of a list of products.
And the requirement is to use the data specified in columns B, C, and D cells, to create comments about the delivery status of the products in column E.
Then, considering the combine text from two or more cells into one celldefinition, as explained earlier, we can use the Ampersand sign-based formula in the target cells. The formula will concatenate the given cell reference values, texts, and characters entered manually to create sentences in the target cells.
- Step 1: Choose cell E3, enter the ‘=’ sign, the required text within double quotes, as shown below. And then enter the Ampersand sign.
Next, enter the cell reference B3 to display the product name, followed by the Ampersand sign.
And then, enter the required text in double quotes, followed by the ‘&’ symbol.
Next, enter the Excel TEXT function to display the order date in the specified Excel date format. Otherwise, Excel will show the date as the equivalent serial number in the output. And then enter the ‘&’ symbol.
Enter the next required text in double quotes, followed by the Ampersand sign.
Finally, enter the cell reference D3 to display the delivery status for the specified product.
=”The delivery status of the product, “&B3&”, ordered on “&TEXT(C3,”DD-MMM-YYYY”)&” is: “&D3
- Step 2: Press Enter to execute the formula and view the desired comment in the target cell.
- Step 3: Using the fill handle, update the formula in the remaining target cells.
Thus, the Ampersand sign combines texts specified in double quotes and cell reference values and displays them in the same order as cited in the formula.
Important Things To Note
- When we use the Ampersand symbol to concatenate or combine text from two or more cells into one cell, it does not insert delimiters automatically. So, we must specify the delimiters in the Ampersand symbol-based concatenation formula at the positions where we require them in the concatenated data.
- The Ampersand symbol-based concatenation formulais case-sensitive. So, we must provide the text values in the required cases for concatenation.
- Consider we need to combine cell values which are numbers, such as time values, dates, and percentages. Then, we must use the TEXT() in the concatenation formula to show the numeric values in the required number format in the output.
Frequently Asked Questions (FAQs)
The process of combining text from different cells into one is called concatenation.
And Excel offers the function CONCATENATE to perform concatenation of the specified text values from multiple cells into one cell.
But the function got replaced by the latest function CONCAT, with the same functionality, in the Excel 2016 version. And hence, we can use the CONCAT() or the CONCATENATE(), available as a Compatibility function, if our Excel version is 2016 or above.
The process of combining two or more text strings from different cells into one is the functionality of the CONCAT().
For example, the table below lists the top stocks in the US and their YTD total returns as of May 24th of this year.
And the requirement is to update the Year to Date Returns statement for each stock in column D based on the given data in columns B and C cells.
Then, as the above scenario is a combine text from two or more cells into one cell example, we can use the CONCAT() in the target cells to achieve the required output.
• Step 1: Choose cell D3, enter the CONCAT(), and press Enter.
=CONCAT(“The YTD total returns through May 24, 2023 for the stock “,B3,” is “,TEXT(C3,”0.00%”))
• Step 2: Enter the formula in cells D4:D7 using the fill handle.
Firstly, we use the TEXT() to show the column C values as percentages in the concatenated text. Otherwise, the CONCAT() will display the values as decimals in the output.
And then, the CONCAT() combines the four argument values (the texts and cell reference values) in the specified order to return a concatenated text as the required statement.
The TEXTJOIN() combines the text from different cells, multiple ranges and or strings.
And, unlike the CONCAT(), TEXTJOIN() includes the delimiter we specify as an argument value. And it inserts the delimiter between the text values we aim to combine.
For example, the first table lists teams and the students in each team.
And the requirement is to display the students in each team, as a comma-separated list, in column E of the second table. Then, we can use the TEXTJOIN() in the target cells to achieve the required output.
• Step 1: Choose cell E1, enter the TEXTJOIN(), and press Enter.
=TEXTJOIN(“, “,TRUE,B2:B5)
• Step 2: Choose cell E2, enter the TEXTJOIN(), and press Enter.
=TEXTJOIN(“, “,TRUE,B6:B9)
• Step 3: Choose cell E3, enter the TEXTJOIN(), and press Enter.
=TEXTJOIN(“, “,TRUE,B10:B13)
The first argument in the TEXTJOIN() is the delimiter we wish to insert between each text value in the concatenated text. Here, we supply a comma and a space character as the delimiter. And since they are special characters, we must provide them in double quotes.
The next argument value is TRUE, indicating the formula to ignore empty cells. And then, we provide the cell range containing the texts to combine.
Finally, the TEXTJOIN() merges the specified cell text values and returns a concatenated text containing the cited delimiter between each input text value.
Download Template
This article must be helpful to understand the Combine Text From Two or More Cells into One Cell, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Combine Text From Two Or More Cells Into One Cell. We explain how to do it, with examples & points to remember. You can learn more from the following articles –
Leave a Reply