How to combine text from two cells in excel

Answer

To combine text from two cells in Excel, use the ampersand (&) operator, the CONCAT function, or the TEXTJOIN function.

Using the Ampersand (&) Operator

The ampersand operator joins text from multiple cells by placing & between cell references.

Basic Syntax:

Enter the formula =A1&B1 to merge text from cells A1 and B1.

Steps:

1. Click the cell where you want the combined text to appear.
2. Type = followed by the first cell reference.
3. Type & after the cell reference.
4. Type the second cell reference.
5. Press Enter to complete the formula.

Adding Spaces Between Text:

Enter =A1&" "&B1 to insert a space between the combined values. The quotation marks contain the space character that appears between the text from both cells.

Windows and Mac Difference:

Both operating systems use identical syntax for the ampersand operator.

Using the CONCAT Function

CONCAT combines text from multiple cells into one cell.

Basic Syntax:

Enter =CONCAT(A1,B1) to merge text from cells A1 and B1.

Steps:

1. Select the destination cell.
2. Type =CONCAT(.
3. Enter the first cell reference.
4. Type a comma.
5. Enter the second cell reference.
6. Type the closing parenthesis.
7. Press Enter.

Adding Delimiters:

Enter =CONCAT(A1," ",B1) to add a space between the text values. Place any delimiter character within quotation marks as a separate argument.

Windows and Mac Difference:

CONCAT functions identically on both platforms.

Using the TEXTJOIN Function

TEXTJOIN combines text from multiple cells with a specified delimiter between each value.

Basic Syntax:

Enter =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) to merge text with automatic delimiter insertion.

Steps:

1. Click the target cell.
2. Type =TEXTJOIN(.
3. Enter the delimiter in quotation marks (such as " " for space or "," for comma).
4. Type a comma.
5. Enter TRUE to skip empty cells or FALSE to include them.
6. Type a comma.
7. Enter the first cell reference or range.
8. Add additional cell references separated by commas.
9. Type the closing parenthesis.
10. Press Enter.

Example Formula:

Enter =TEXTJOIN(" ",TRUE,A1:A5) to combine all text from cells A1 through A5 with spaces between each value, skipping any empty cells.

Windows and Mac Difference:

TEXTJOIN works the same way on both operating systems. This function requires Excel 2019 or later, or Microsoft 365 subscription on both platforms.

Combining Text with Numbers

Excel automatically converts numbers to text when using concatenation methods.

Enter =A1&B1 where A1 contains "Revenue: " and B1 contains 5000 to display "Revenue: 5000" in the result cell.

Formatting Numbers:

Use the TEXT function to control number formatting during concatenation.

Enter =A1&TEXT(B1,"$#,##0.00") to display the number from B1 with currency formatting. The format code within quotation marks determines how the number appears.

Combining Text from Multiple Cells

Using Multiple Ampersands:

Enter =A1&" "&B1&" "&C1 to join text from three cells with spaces between them. Add & between each cell reference and delimiter.

Using CONCAT with Ranges:

Enter =CONCAT(A1:C1) to merge all text from cells A1 through C1 without delimiters. CONCAT processes the range from left to right.

Using TEXTJOIN with Ranges:

Enter =TEXTJOIN(", ",TRUE,A1:C1) to combine cells A1 through C1 with comma-space separation. This method provides the most flexibility for combining multiple cells.

Flash Fill for Pattern Recognition

Flash Fill detects patterns and automatically combines text without formulas.

Steps:

1. Type the desired combined result manually in the first cell of your target column.
2. Press Enter to move to the next cell.
3. Begin typing the second combined result.
4. Press Ctrl+E (Windows) or Cmd+E (Mac) to trigger Flash Fill.
5. Review the automatically filled values.
6. Press Enter to accept or Esc to cancel.

Windows and Mac Difference:

Flash Fill uses Ctrl+E on Windows and Cmd+E on Mac. Flash Fill requires Excel 2013 or later on Windows and Excel 2016 or later on Mac.

Removing Extra Spaces

TRIM removes extra spaces from combined text.

Enter =TRIM(A1&" "&B1) to combine text and eliminate unnecessary spaces. TRIM keeps only single spaces between words and removes leading or trailing spaces.

Combining First and Last Names

Enter =A2&" "&B2 where A2 contains first names and B2 contains last names to create full names in a new column. Drag the fill handle down to apply the formula to multiple rows.

Alternative with TEXTJOIN:

Enter =TEXTJOIN(" ",TRUE,A2,B2) to achieve the same result with automatic empty cell handling.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report