Concatenated in Excel: A Practical Guide
Learn how to concatenate in Excel using &, CONCAT, CONCATENATE, and TEXTJOIN with practical examples, tips, and common pitfalls. A comprehensive, step-by-step resource from XLS Library.
Concatenation in Excel is the process of joining text from two or more cells into a single string. You can accomplish this with the ampersand operator, the CONCAT or CONCATENATE functions, or TEXTJOIN for delimited joins. This guide explains when to use each method, with clear examples and best practices.
What concatenation means in Excel
Concatenation in Excel is the act of taking text from two or more cells and combining it into one continuous string. This is a common operation for creating full names from first and last name columns, building email addresses from a user name and domain, or generating codes by stitching together parts of an identifier. When you concatenate, you decide how the final string should look, including whether to insert spaces, commas, or other delimiters. You also need to consider how to treat numbers or dates that are included as text, since Excel may interpret them differently once joined. The concept is foundational for data preparation and cleaning, because tidy, readable strings make reporting and sorting more reliable. In this guide, you’ll learn several reliable methods to concatenate data in Excel, their best-use scenarios, and practical examples you can adapt to your own datasets.
wordCount=160
Tools & Materials
- Computer or device with Excel installed(Excel for Microsoft 365 or Excel 2016+ is ideal)
- Sample workbook with text fields(At least two columns of text (e.g., FirstName, LastName) and optional delimiter columns)
- Optional: Text editor(For drafting custom delimiters or notes)
- Reference table or sheet(Useful for practicing with multiple ranges)
Steps
Estimated time: 20-35 minutes
- 1
Open the workbook and inspect data
Launch Excel and review the columns you will join. Identify which cells or ranges contain the text you want to concatenate and decide on a delimiter (space, comma, dash, etc.). This preparation prevents surprises later in the process.
Tip: Document the exact delimiter you plan to use so future users understand the output. - 2
Choose a method based on your needs
If you’re joining a small number of cells, the ampersand operator is quick and readable. For larger joins or if you want to join ranges with a delimiter, TEXTJOIN is usually the best choice. Consider compatibility with older versions when selecting a function.
Tip: Prefer readability; use descriptive names for helper columns if needed. - 3
Concatenate with the ampersand (&) operator
In a target cell, type =A2 & " " & B2 to combine first and last name with a space. You can adjust the delimiter by changing the string between quotes.
Tip: If you’re copying down, ensure there are no merged cells that might disrupt the fill. - 4
Use the CONCAT function for a fixed list
For joining two or more text arguments, =CONCAT(A2,B2) works well. It doesn’t insert delimiters unless you specify them explicitly.
Tip: CONCAT can handle ranges like =CONCAT(A2:A4) but it will omit separators unless you insert them manually. - 5
Use the CONCATENATE function for legacy compatibility
Older workbooks may still rely on =CONCATENATE(A2,B2). Functionally identical to CONCAT but kept for backward compatibility.
Tip: If you start a new workbook, prefer CONCAT or TEXTJOIN for future-proofing. - 6
Use TEXTJOIN for delimited joins across ranges
To join a list with a delimiter, use =TEXTJOIN(" ", TRUE, A2:A4). The first argument is the delimiter, the second controls whether to ignore empty cells, and the remaining arguments are the texts or ranges.
Tip: TEXTJOIN is especially powerful for combining many values without creating long nested formulas. - 7
Handle dates and numbers properly
Dates and numbers are treated as text when concatenated. Use TEXT to format numbers and dates (e.g., =TEXT(B2, "0.00") or =TEXT(C2, "mm/dd/yyyy").
Tip: Applying a consistent format early prevents inconsistent outputs later. - 8
Validate results and copy down safely
After building your formula, copy it down to verify consistency across the dataset. Use TRIM to remove unintended spaces and test edge cases like blanks or non-text values.
Tip: Consider adding a hidden helper column to flag unexpected results.
People Also Ask
What does concatenation mean in Excel?
Concatenation means joining text from multiple cells into a single string. It’s useful for creating full names, emails, or codes by combining parts of data. Choose a method based on your needs and Excel version.
Concatenation in Excel means joining text from several cells into one string for easier reporting.
How do I concatenate text in Excel without spaces?
Use the ampersand operator without adding a delimiter, e.g., =A2&B2. If you need a delimiter later, insert it explicitly, e.g., =A2 & "-" & B2.
Use the ampersand sign without extra quotes to join without spaces.
What is the difference between CONCAT, CONCATENATE, and TEXTJOIN?
CONCAT and CONCATENATE combine text; TEXTJOIN also supports a delimiter and can ignore empty cells. TEXTJOIN is best for large ranges with a delimiter, while CONCAT/CONCATENATE are simpler for small joins.
TEXTJOIN adds a delimiter and optional empty-cell handling; CONCAT is a modern replacement for CONCATENATE.
Can I concatenate numbers or dates?
Yes. Numbers and dates are converted to text in concatenation. Use TEXT to format numbers and dates before joining, e.g., =TEXT(B2, "0.00") or =TEXT(C2, "mm/dd/yyyy").
Numbers and dates become text when concatenated; format them with TEXT if needed.
What if some cells are blank?
TEXTJOIN can ignore blanks by using TRUE for the ignore_empty argument. Other methods will include empty strings if not handled, so consider wrapping parts with IF or TRIM.
If blanks appear, TEXTJOIN can skip them; otherwise handle blanks with IF or TRIM.
Is TEXTJOIN available in all Excel versions?
TEXTJOIN is available in Excel 2019 and Microsoft 365. If you’re using earlier versions, use CONCAT/CONCATENATE with explicit delimiters or a helper formula.
TEXTJOIN isn’t in all versions; use older functions if needed.
Watch Video
The Essentials
- Choose method based on delimiter needs.
- TEXTJOIN simplifies joining many cells with a delimiter.
- Trim spaces to avoid extra gaps.
- Dates and numbers may become text when concatenated.
