How to combine text from two columns in excel

Answer

To combine text from two columns in Excel, you can use several methods depending on your version of Excel and the desired output format. Combining text is also called “concatenation.”

1. Using the Ampersand (&) Operator

A simple way to join text from two columns is by using the & symbol.

Steps:

1. Select the cell where you want the combined text to appear.

2. Enter the formula:

   =A1 & B1

   This combines the text in cell A1 and B1 directly.

3. Press Enter.

4. Drag the fill handle down to apply the formula to other rows.

Example:

• Column A: John

• Column B: Doe

• Formula: =A1 & " " & B1

• Result: John Doe

Adding " " ensures there is a space between the first and last names.

2. Using the CONCAT Function

The CONCAT function is a modern replacement for CONCATENATE in recent Excel versions.

Steps:

1. Select the cell for the result.

2. Enter the formula:

   =CONCAT(A1, " ", B1)

3. Press Enter.

4. Fill down the formula to other cells.

Example:

• =CONCAT(A1, " - ", B1) results in John - Doe

This allows custom separators between text.

3. Using the TEXTJOIN Function

TEXTJOIN is useful when combining multiple columns with a delimiter and ignoring empty cells.

Steps:

1. Select the result cell.

2. Enter the formula:

   =TEXTJOIN(" ", TRUE, A1, B1)

   • " " is the delimiter.

   • TRUE ignores empty cells.

3. Press Enter.

4. Copy the formula to other rows.

Example:

• If A2 is empty and B2 contains Smith, the result is Smith instead of leaving a double space.

4. Using Flash Fill (No Formula Needed)

Excel can automatically fill combined text using Flash Fill.

Steps:

1. Type the combined text manually in the first cell (e.g., John Doe).

2. Start typing the second row. Excel will suggest the pattern.

3. Press Enter to accept the suggestion.

4. Alternatively, go to Data → Flash Fill or press Ctrl + E.

Key Notes

• Use & for quick, simple combinations.

• Use CONCAT for flexible formulas in newer Excel versions.

• Use TEXTJOIN when dealing with multiple columns or ignoring blanks.

• Flash Fill is practical for non-formula solutions.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report