How to Combine Text from Two Cells in Excel
Learn proven methods to combine text from two cells in Excel using &, CONCAT, CONCATENATE, and TEXTJOIN. Includes examples, handling blanks, delimiters, and real-world tips for clean, reliable data in Excel.
By the end of this guide you will be able to combine text from two cells in Excel using CONCAT, CONCATENATE, TEXTJOIN, or the ampersand operator. The methods show how to handle blanks, apply a delimiter, and preserve formatting while copying the result down your sheet. Practical examples cover common data scenarios and troubleshooting tips.
What it means to combine text in Excel
In data preparation and reporting, you often need to join the contents of two cells into a single field. This is called concatenation. In Excel, you have several reliable ways to do this: the ampersand operator, the CONCAT, CONCATENATE, and TEXTJOIN functions. These methods work with text and numbers (after formatting) and help you normalize data for dashboards, exports, and shared spreadsheets. According to XLS Library, understanding these options gives you flexibility and clarity when building reports.
Core methods to join text
The main methods fall into two camps: simple operators and dedicated text functions. For quick jobs on a single row, the ampersand (&) is fast and readable. For multi-cell joins with separators, TEXTJOIN shines. CONCAT and CONCATENATE are function-based options that combine cell ranges, with CONCAT being the modern replacement for CONCATENATE. Each method has its own syntax and caveats, so choosing the right one depends on your data and Excel version. This guide sticks to practical, tested patterns you can reuse today.
Method 1: The ampersand (&) operator
The ampersand operator is the simplest way to join two cells. Example: =A2 & " " & B2 produces a space-delimited result. Why use it? It’s highly legible, works in all modern Excel versions, and uses the default text formatting of the operands. If either A2 or B2 is numeric, Excel converts it to text automatically, which is convenient but watch formatting if you need leading zeros.
Method 2: The CONCAT function
The CONCAT function provides a modern alternative for joining text. Syntax: =CONCAT(A2,B2) will merge the contents of A2 and B2 without a delimiter. If you need a delimiter, you must add it explicitly: =CONCAT(A2, " ", B2). CONCAT can handle ranges, which is handy when you want to join many cells in a row.
Method 3: The CONCATENATE function
CONCATENATE is the legacy function and remains supported for compatibility with older workbooks. Syntax: =CONCATENATE(A2,B2) yields the same result as CONCAT(A2,B2) for two cells. Still, most new workbooks prefer CONCAT because it's shorter and more versatile.
Method 4: The TEXTJOIN function
TEXTJOIN is designed for joining many cells with a common delimiter. Syntax: =TEXTJOIN(" ", TRUE, A2,B2) joins the contents of A2 and B2 with a single space while ignoring blanks (ignore_empty=TRUE). If you want a comma and space, use =TEXTJOIN(", ", TRUE, A2,B2). TEXTJOIN won’t insert a delimiter if both cells are empty.
Handling blanks and delimiters
Delimiters matter. Using TEXTJOIN with ignore_empty set to TRUE prevents extra separators from appearing when a cell is blank. If you use the ampersand, you must manually manage blanks, e.g., =IF(OR(A2="",B2=""), A2 & B2, A2 & " " & B2). For a more complex join (e.g., conditional separators), you can nest IF statements or use TEXTJOIN with an array.
Practical examples: formulas in action
- Simple join: =A2 & " " & B2
- With a single delimiter: =TEXTJOIN(" - ", TRUE, A2, B2)
- Range join: =TEXTJOIN(", ", TRUE, A2:B2) for two cells in a row or extend to a larger range
- Formatting numbers: =TEXT(A2,"0") & " " & B2
- Adaptive join when second cell may be blank: =IF(B2="","",A2 & " " & B2)
Copying formulas down and trimming results
Drag the fill handle from the bottom-right corner of the formula cell to apply to adjacent rows. Excel will adjust A2/B2 references (A3/B3, etc.). If your data includes headers, consider using structured references or absolute references to lock column letters when needed.
Common errors and how to fix them
#NAME? usually signals a typo in the function name or quotes. #VALUE! can appear when trying to combine non-text data without proper formatting. Ensure any delimiters are enclosed in quotes, and avoid smart quotes copied from word processors. If TEXTJOIN isn’t available in older Excel versions, fall back to CONCAT/&, or CONCATENATE.
Real-world scenarios: business-friendly examples
- Customer names in A2 and B2: =A2 & " " & B2 to build full names for invoices.
- Address lines: =TEXTJOIN(", ", TRUE, A2,B2,C2) to create a single address line for shipping labels.
- Product labels: =A2 & " - " & B2 to generate SKU descriptions for catalogs.
Bonus tips: formatting numbers when concatenating
If you join numbers with text, use TEXT to control decimals and separators: =TEXT(A2, "0.00") & " units" for consistent reporting. For large datasets, TEXTJOIN with a delimiter reduces formula length and keeps your workbook cleaner. Remember to test results with edge cases (blanks, leading zeros) to ensure reliability.
Quick reference cheat sheet
- & operator: =A2 & B2
- CONCAT: =CONCAT(A2,B2)
- CONCATENATE: =CONCATENATE(A2,B2)
- TEXTJOIN: =TEXTJOIN(separator, ignore_empty, range1, [range2,...])
- When to use: simple two-cell join with &, multi-cell join with TEXTJOIN and ranges.
AUTHORITY SOURCES
- https://learn.microsoft.com/en-us/office/troubleshoot/excel/concatenate-function
- https://learn.microsoft.com/en-us/office/troubleshoot/excel/textjoin-function
- https://en.wikipedia.org/wiki/Concatenation
Tools & Materials
- Microsoft Excel (Windows or macOS)(Office 365/Excel 2019+ recommended for TEXTJOIN; older versions support & and CONCAT/CONCATENATE.)
- Sample workbook with two text cells (e.g., A2 and B2)(Create or copy a small dataset to test joins)
- Optional: a delimiter guide (space, comma, dash, etc.)(Useful for testing different join styles)
- If using Excel on Mac, ensure TEXTJOIN is available(TEXTJOIN availability may vary by version)
Steps
Estimated time: 15-20 minutes
- 1
Prepare and review data
Identify the two cells you want to join (e.g., A2 and B2). Ensure there are no stray characters that could interfere with the join, and decide whether you want a delimiter between the values.
Tip: Keep your data consistent in type (text) to avoid unexpected formatting. - 2
Choose your join method
Decide between the ampersand, CONCAT, CONCATENATE, or TEXTJOIN based on the use case and Excel version. TEXTJOIN is best for multiple cells with a delimiter.
Tip: TEXTJOIN with ignore_empty=TRUE is a clean default for mixed blanks. - 3
Join with ampersand
Enter =A2 & " " & B2 to join with a space. Adjust the delimiter as needed and consider number-to-text behavior when necessary.
Tip: Use quotes for literal text delimiters. - 4
Join with CONCAT
Enter =CONCAT(A2, " ", B2) to achieve the same as the ampersand with a delimiter.
Tip: Great for shorter, readable formulas in newer Excel versions. - 5
Join with TEXTJOIN
Enter =TEXTJOIN(" ", TRUE, A2, B2) to join with a delimiter while ignoring blanks.
Tip: TEXTJOIN supports ranges, perfect for longer joins. - 6
Copy formulas down
Use the fill handle to apply the formula to adjacent rows. Excel will adjust references automatically.
Tip: If you base your formula on a named table or structured references, the behavior is even more predictable. - 7
Test with edge cases
Check how blanks and numbers behave in your formulas and adjust with TEXT or conditional logic if needed.
Tip: Edge cases reveal where your delimiter logic may fail. - 8
Document your approach
Comment or annotate formulas so teammates understand the chosen method and delimiter choices.
Tip: Clear documentation reduces errors in collaborative workbooks.
People Also Ask
What is the easiest way to combine two cells in Excel?
The ampersand operator (&) is the easiest and fastest way for a simple two-cell join, e.g., =A2 & " " & B2. TEXTJOIN is a better choice when you join many cells with a delimiter. Choose based on your workbook version and data pattern.
Use the ampersand for a quick join, or TEXTJOIN when you have many cells to combine with a delimiter.
Can I ignore blanks when joining text?
Yes. TEXTJOIN supports an ignore_empty argument. Use =TEXTJOIN(" ", TRUE, A2, B2) to insert a single space between non-blank items. If you use &, you must manually handle blanks with IF statements.
TEXTJOIN can ignore blanks automatically; use it when joining multiple cells with separators.
How do I add a delimiter between two texts?
Include the delimiter as a literal in the formula, for example =A2 & " - " & B2 or =TEXTJOIN(" - ", TRUE, A2, B2). Delimiters can be any text string.
Put the delimiter inside quotes in &, or use TEXTJOIN with the delimiter argument.
Is CONCATENATE still needed in modern workbooks?
CONCATENATE is the legacy function and is kept for compatibility. In modern workbooks, CONCAT or TEXTJOIN is preferred for clarity and flexibility.
Use CONCAT or TEXTJOIN in new workbooks; CONCATENATE is mainly for compatibility.
How can I join more than two cells efficiently?
TEXTJOIN is designed for multiple cells and ranges, e.g., =TEXTJOIN(", ", TRUE, A2:C2). For two cells, any method works, but TEXTJOIN scales well for bigger datasets.
TEXTJOIN scales nicely for many cells with a single delimiter.
What about numeric values and formatting when joining?
Format numbers with TEXT before joining, e.g., =TEXT(A2, "0.00") & " units". This keeps decimal places consistent in reports.
Format numbers with TEXT to control decimals when joining with text.
Watch Video
The Essentials
- Choose the right method for your data size and Excel version.
- TEXTJOIN handles many cells and blanks gracefully.
- Ampersand is fast for simple two-cell joins.
- Format numbers when concatenating to preserve readability.
- Test formulas across rows to catch edge cases early.
