How to combine two cells of text in excel

Answer

To combine two cells of text in Excel, you use concatenation methods, including the CONCATENATE function, CONCAT function, ampersand operator (&), or TEXTJOIN function.

What Methods Can Combine Text from Multiple Cells

Excel provides four primary methods for combining text: the ampersand operator (&), the CONCATENATE function, the CONCAT function, and the TEXTJOIN function. The ampersand operator works in all Excel versions. The CONCATENATE function operates in Excel 2016 and earlier versions. The CONCAT function became available starting with Excel 2019. The TEXTJOIN function exists in Excel 2019 and Microsoft 365.

How to Use the Ampersand Operator (&)

The ampersand operator (&) combines text by placing it between cell references. Type =A1&B1 in cell C1 to merge contents from cells A1 and B1. Add spaces between combined text using quotation marks: =A1&" "&B1. This method handles up to 255 text strings per formula.

Steps for Windows and Mac

1. Select the destination cell
2. Type the equals sign (=)
3. Click the first cell reference
4. Type the ampersand symbol (&)
5. Click the second cell reference
6. Press Enter (Windows) or Return (Mac)

How to Use CONCATENATE Function

CONCATENATE function joins up to 30 text items in Excel versions before 2019. The syntax follows =CONCATENATE(text1, text2, text3). Enter =CONCATENATE(A1,B1) to combine cells A1 and B1. Include spaces by adding them within quotation marks: =CONCATENATE(A1," ",B1).

Function Syntax

• text1: Required first text item
• text2: Optional second text item
• text3-text30: Optional additional text items

How to Use the The CONCAT Function

CONCAT function replaces CONCATENATE in Excel 2019 and Microsoft 365. This function accepts ranges and individual cell references. Type =CONCAT(A1:B1) to combine cells A1 and B1. The function processes up to 253 text arguments with a 32,767 character limit per argument.

Key Differences from CONCATENATE

• Accepts cell ranges (A1:B1)
• Handles 253 arguments versus 30 arguments
• Processes arrays without Ctrl+Shift+Enter

How to Use the TEXTJOIN Function

TEXTJOIN function combines text with specified delimiters in Excel 2019 and Microsoft 365. The syntax reads =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...). Enter =TEXTJOIN(" ",TRUE,A1:B1) to combine A1 and B1 with a space delimiter.

Function Parameters

• delimiter: Separator between text strings
• ignore_empty: TRUE skips empty cells, FALSE includes them
• text1: First text string or range
• text2: Optional additional text strings or ranges

How to Add Separators Between Combined Text

Separators improve readability in combined text strings. Use quotation marks around separators in formulas. Common separators include spaces (" "), commas (","), hyphens ("-"), and pipe symbols ("|"). Place separators between cell references: =A1&", "&B1.

How to Combine Text with Numbers and Dates

Numbers and dates require TEXT function formatting when combining with text. Use =A1&" - "&TEXT(B1,"$#,##0.00") for currency formatting. Apply =A1&" "&TEXT(B1,"MM/DD/YYYY") for date formatting. The TEXT function preserves number formats during concatenation.

Common Format Codes

• "0.00": Two decimal places
• "$#,##0": Currency without decimals
• "MM/DD/YYYY": Date format
• "0%": Percentage format

How to Handle Empty Cells in Concatenation

Empty cells create unwanted spaces or separators in combined text. TEXTJOIN function's ignore_empty parameter eliminates empty cell issues. Set ignore_empty to TRUE: =TEXTJOIN(" ",TRUE,A1:D1). Traditional methods require IF statements: =IF(A1="","",A1)&IF(B1="","",B1).

How to Combine Text Across Multiple Rows

Combining text across rows requires array formulas or TEXTJOIN function. TEXTJOIN simplifies multi-row concatenation: =TEXTJOIN(" ",TRUE,A1:A10). Array formulas need Ctrl+Shift+Enter in older Excel versions. Microsoft 365 handles arrays automatically without special key combinations.

Common Errors and Solutions

#VALUE! error occurs with incompatible data types. Convert numbers using TEXT function before concatenation. #NAME? error indicates misspelled function names or unavailable functions in your Excel version. Character limit exceeded generates #VALUE! error past 32,767 characters. Circular reference errors happen when formulas reference their own cells.

Troubleshooting Steps

1. Verify cell references point to correct locations
2. Check quotation marks around text strings
3. Confirm function availability in Excel version
4. Test with smaller data sets first
5. Remove circular references from formulas

Yes · 0 No · 0 Add Comment · 0

Add ·

Report