Learning excel string concat is one of the most useful skills you can develop because joining text from multiple cells, ranges, or literal strings appears in nearly every real-world spreadsheet task. Whether you are building customer full names from first and last name columns, generating SKUs from product attributes, creating dynamic file paths, or merging address fields for mail merges, concatenation lets you transform raw data into something readable, exportable, and ready for downstream systems. Excel offers four primary tools for this job, and each has its own quirks worth understanding.
The four main approaches are the ampersand operator (&), the legacy CONCATENATE function, the modern CONCAT function introduced in Excel 2019 and Microsoft 365, and TEXTJOIN which adds delimiter and ignore-empty support. Each of these has tradeoffs around range support, delimiter handling, blank cells, and backward compatibility. For example, CONCAT can take an entire range like A1:A100, while CONCATENATE requires you to list each cell individually. TEXTJOIN goes a step further by inserting a separator automatically between every value.
Most everyday workbook tasks lean on the ampersand because it is short, intuitive, and works in every modern Excel version. A formula like =A2&" "&B2 produces "John Smith" from two columns instantly. But when you need to combine 30 cells or join an entire column of values with commas, ampersand becomes painful to type and impossible to maintain. That is exactly where CONCAT and TEXTJOIN shine, and learning when to switch between them is a hallmark of an efficient Excel user.
Beyond the basic syntax, real string concatenation work involves edge cases that trip up beginners. Numbers lose their formatting when concatenated unless wrapped in TEXT(). Dates become serial numbers. Empty cells leave double spaces or trailing delimiters. Long results can exceed the 32,767-character cell limit. Mixing arrays and scalars produces unexpected spills in dynamic-array Excel. Each of these has a documented workaround, and this guide will walk through them one by one so you stop hitting the same walls.
String concatenation also intersects with other Excel skills that boost your productivity. You will often combine concatenation with TEXT for formatting, with TRIM to clean spaces, with SUBSTITUTE to swap characters, with IF to conditionally include parts, and with lookup functions to pull values from other sheets before joining them. Mastering this intersection turns simple joins into powerful data transformation pipelines that can replace dozens of manual copy-paste steps.
This guide is organized to take you from absolute basics through advanced patterns. You will see clear syntax tables, working examples, side-by-side comparisons of CONCAT vs CONCATENATE vs TEXTJOIN vs &, and the most common pitfalls Excel users hit when joining text. By the end you will know exactly which method to reach for in any situation, how to handle numbers and dates cleanly, and how to scale concatenation from two cells to thousands of rows without breaking your workbook.
Along the way the tutorial also touches on related tasks people frequently look up alongside concatenation, like splitting text back apart with TEXTSPLIT, working with delimiters in Power Query, and using the new dynamic-array spilling behavior to write shorter formulas. Each section ends with a practical example you can paste into your own sheet, and there are quizzes at the end of each main block so you can test your understanding before moving on to the next concept.
The simplest and most universal method. Type =A1&B1 to join two cells, or =A1&" "&B1 to add a space. Works in every Excel version since the early 1990s and is unbeatable for short joins of two to five values.
The legacy function that takes a comma-separated list of text values. Syntax: =CONCATENATE(A1," ",B1). Still supported for backward compatibility, but Microsoft now recommends CONCAT instead because it accepts ranges and is shorter to type.
The modern replacement for CONCATENATE, available from Excel 2019 and Microsoft 365. Accepts ranges like =CONCAT(A1:A10), which dramatically cuts formula length when joining many cells. Does not insert delimiters automatically.
The most powerful option for delimiter-based joins. Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, ...). It inserts your chosen separator between every value and can skip blank cells, which is perfect for building CSV strings or sentence lists.
For one-off transformations on large datasets, the Power Query Merge Columns command in the Transform tab gives a no-formula option. It applies a delimiter, handles nulls cleanly, and re-runs automatically when source data refreshes.
The ampersand operator is the foundation of excel string concat and the method most users learn first. Its syntax could not be simpler: place an ampersand between any two values you want to glue together, and Excel returns one combined text string. For example, ="Hello, "&A2 produces "Hello, Alice" when A2 contains "Alice". You can chain as many ampersands as you want, which makes it perfect for short, readable joins like full names, ID codes, or labels with embedded values.
One of the strengths of ampersand is that it works identically across every Excel version, from Excel 2003 to Excel for the web. There is no compatibility worry, no need to ask whether your colleague has Microsoft 365 or a perpetual license. If you ship a workbook with ampersand joins, it will open and recalculate correctly anywhere. That universal support is why so many template builders and corporate workbooks still rely on it for everyday name and address fields.
However, the ampersand approach scales badly. If you need to join twenty cells with commas, the formula becomes =A1&", "&A2&", "&A3 and so on for two hundred characters. That is hard to type, hard to read, and impossible to refactor later. The maintenance cost grows with every cell. This is precisely the pain point that CONCAT and TEXTJOIN solve, and it is the moment most analysts switch away from ampersand toward function-based concatenation.
Another subtlety is how ampersand handles non-text inputs. If A1 contains the number 1500 and B1 contains the date 1/15/2026, then =A1&B1 returns "150046037" because Excel converts the date to its underlying serial number before joining. Most users find this surprising. The fix is to wrap each value in TEXT() with an explicit format string, such as =TEXT(A1,"#,##0")&" on "&TEXT(B1,"mm/dd/yyyy"). That produces "1,500 on 01/15/2026" exactly as expected.
Spaces and punctuation deserve a moment of thought. Ampersand never inserts anything automatically, so you must add every literal delimiter inside quotes. A common error is writing =A1&B1 and getting "JohnSmith" with no space. The correct form is =A1&" "&B1 with a quoted space. Similarly, to join with a comma and space use &", "&. To embed a line break inside a cell, use &CHAR(10)& and turn on Wrap Text for that cell so the break actually displays as a new line.
For users coming from another spreadsheet program, the ampersand syntax is portable. Google Sheets, LibreOffice Calc, and Apple Numbers all support the same operator with the same behavior. So a formula like =A1&" - "&B1 works in any of them. This portability is one more reason it remains the default tool for quick joins, even though Microsoft has invested heavily in CONCAT and TEXTJOIN for more advanced scenarios that we will cover next.
One last tip on ampersand: when building formulas that include double quotes inside the final string, you must double the quotes. To produce the string He said "hi" the formula becomes ="He said ""hi""". This escaping confuses many beginners. If your concatenation involves quoted phrases, JSON, or CSV fields with embedded quotes, consider switching to a TEXTJOIN or CONCAT approach where the structure is clearer and the quote-doubling is easier to reason about.
CONCATENATE is the original text-joining function and dates back to Excel 2007 and earlier. Its syntax is =CONCATENATE(text1, text2, ...) where each argument must be supplied individually. You cannot pass a range like A1:A10; you must list A1, A2, A3 and so on. The function accepts up to 253 arguments and returns a single concatenated string. It is still supported in modern Excel for backward compatibility with old workbooks.
Microsoft now considers CONCATENATE deprecated and recommends CONCAT as the replacement. However, if you maintain workbooks that need to open in Excel 2013 or older, sticking with CONCATENATE is the safer choice. The function does not insert delimiters automatically and does not skip blanks, so you still need to handle separators and empty cells manually with extra quotes or IF logic. It performs identically to ampersand for typical two- and three-cell joins.
CONCAT was introduced in Excel 2019 and is the default in Microsoft 365. The big improvement over CONCATENATE is range support: you can write =CONCAT(A1:A100) and Excel will join every cell in that range in row-major order. This single feature replaces dozens of ampersands or arguments when working with column-style data. The function still does not insert delimiters; it simply glues values end to end, so you typically combine it with another column of separators or with TEXTJOIN.
CONCAT also supports multiple ranges and mixed arguments. For example, =CONCAT("ID-", A2:A5, "-end") produces a single string with the prefix, every joined value, and the suffix. It respects the 32,767-character cell limit, so extremely long joins will return a #VALUE! error. If your workbook needs to open in pre-2019 versions, CONCAT will not be recognized and will produce a _xlfn.CONCAT name error.
TEXTJOIN is the most flexible of the three and the right choice whenever you want a consistent separator between values. Its syntax is =TEXTJOIN(delimiter, ignore_empty, text1, text2, ...). The delimiter can be any text including comma-space, pipe, semicolon, or even CHAR(10) for line breaks. The ignore_empty argument is a TRUE/FALSE switch that determines whether blank cells produce duplicate delimiters or get skipped entirely.
TEXTJOIN excels at building CSV strings, bullet lists, and sentence joins. For example, =TEXTJOIN(", ", TRUE, A1:A20) produces "Alice, Bob, Carol" without any trailing commas even if some cells are empty. Like CONCAT, it was released in Excel 2019 and Microsoft 365 only, so it will not work in older versions. The function handles up to 252 text arguments and respects the same 32,767-character cell limit.
If you remember only one formula from this guide, make it =TEXTJOIN(", ", TRUE, A1:A100). The TRUE flag skips empty cells, the comma-space delimiter is reusable for almost any list, and the range argument scales effortlessly from 10 to 10,000 rows. This single pattern replaces dozens of ampersands and handles blanks gracefully without any nested IF logic.
Handling numbers and dates is where most concatenation bugs hide, and understanding the rules will save you hours of debugging. When you concatenate a number, Excel converts it to its raw text representation using the default General format, which strips commas, currency symbols, percentage signs, and any decimal-place formatting you applied to the cell. So a cell that displays $1,500.00 will concatenate as 1500. The fix is to wrap the value in TEXT() with the exact format string you want, like TEXT(A1,"$#,##0.00").
Dates behave even more dramatically because Excel stores them as serial numbers counting days from January 1, 1900. A cell displaying 1/15/2026 has the underlying value 46037, and that is what concatenation will return. Use TEXT(A1,"mm/dd/yyyy") or TEXT(A1,"mmmm d, yyyy") to format the date as readable text. The same applies to times, which are stored as fractional days. TEXT(A1,"h:mm AM/PM") produces a clean clock display ready for joining into a sentence or label.
Blank cells create a different kind of headache. With ampersand and CONCATENATE, an empty cell produces nothing in the output, which sounds fine until you realize the surrounding spaces or commas remain. For example, =A1&" "&B1&" "&C1 with B1 empty produces "John Smith" with a double space. With TEXTJOIN and ignore_empty=TRUE, the function automatically skips B1 and returns "John Smith" cleanly. This single behavior is why TEXTJOIN dominates whenever data quality is uneven.
The 32,767-character cell limit is rarely hit but worth knowing about. If you concatenate an entire column of long product descriptions, you may exceed the limit and see a #VALUE! error. The workaround is to split the result across multiple cells or to use Power Query, which writes results as table columns rather than single-cell text. Power Query also gives you a cleaner refresh path if the source data changes frequently, which is common in reporting workflows.
Leading and trailing spaces in source cells are another common source of ugly output. A user might type " Alice" with a stray space, and your concatenation will faithfully include it. TRIM() removes leading, trailing, and double internal spaces in one step. Wrap each input as TRIM(A1) inside your formula, or run a one-time cleanup pass on the source column. CLEAN() removes non-printable control characters like tabs and line feeds, which sometimes sneak in from copy-paste from PDF or web sources.
For multi-line output, CHAR(10) on Windows or CHAR(13) on older Mac builds produces a newline character. So =TEXTJOIN(CHAR(10),TRUE,A1:A5) creates a five-line address block in one cell, as long as you turn on Wrap Text in the Home tab. This pattern is the foundation of cell-based address labels, single-cell receipts, and any place where you want stacked text without merged cells. Be sure to widen the row height after enabling Wrap Text so all lines become visible.
Finally, when your concatenation involves lookup results from another sheet, perform the lookup first and concatenate second. A formula like =VLOOKUP(A1,Table,2,FALSE)&" - "&VLOOKUP(A1,Table,3,FALSE) works but recalculates the lookup twice. For performance on large sheets, put each lookup in a helper column and concatenate those helpers. In Microsoft 365 you can use LET() to define each lookup once and reuse it, which keeps the formula short while avoiding duplicate work.
Advanced concatenation patterns combine the basic functions with lookups, logic, and dynamic arrays to solve real business problems. A classic example is building a full mailing address from separate street, city, state, and ZIP columns. The formula =TEXTJOIN(CHAR(10), TRUE, A2, B2&", "&C2&" "&D2) joins the street on line one and the city-state-ZIP combo on line two, ready for a single-cell label. Add TRIM around each input to guard against stray spaces from imported data.
Conditional concatenation uses IF inside the function arguments to include or exclude parts. For instance, =A2&IF(B2="",""," ("&B2&")") appends a parenthetical nickname only when B2 contains a value. This pattern keeps output clean when optional fields are sometimes blank. A more compact alternative in Microsoft 365 is =A2&IFS(B2="","",TRUE," ("&B2&")"), and for many optional fields TEXTJOIN with ignore_empty=TRUE simply skips the blanks for you without any IF wrapper at all.
Dynamic arrays change the game for concatenation. In Microsoft 365 you can write =A2:A100&" "&B2:B100 in a single cell and Excel will spill 99 results down the column automatically, with no fill-down required. This is faster, easier to refactor, and easier to spot-check than 99 separate formulas. Combine spilling with FILTER and SORT to build dynamic concatenated lists that update as your source data changes, perfect for dashboards and summary cards.
Building delimited keys for VLOOKUP, INDEX-MATCH, or XLOOKUP joins is another common pattern. When your lookup table uses a composite key like "region-product", you can concatenate the lookup value as A2&"-"&B2 inside the VLOOKUP first argument. This avoids creating a helper column, though for very large sheets a helper column is usually faster because it eliminates redundant concatenation on every lookup recalculation cycle.
Reversing a concatenation, sometimes called splitting, is the natural counterpart to joining. Excel 2019 and 365 introduced TEXTSPLIT, which takes a delimited string and returns the parts as a spilled array. The classic ampersand-based approach using LEFT, RIGHT, FIND, and LEN still works in every version but is far more verbose. Power Query Split Column by Delimiter is the no-formula option and handles edge cases like quoted delimiters and embedded line breaks cleanly.
SUBSTITUTE pairs beautifully with concatenation when you need to swap characters in a joined result. For example, =SUBSTITUTE(TEXTJOIN(",", TRUE, A1:A10), ",", " and ", COUNTA(A1:A10)-1) produces a grammar-friendly list like "apple, banana and cherry" by replacing only the last comma with the word "and". This pattern is common in invoice line-item summaries and email body templates where the natural-language tone matters.
Finally, performance scales surprisingly well. A workbook with 100,000 rows of three-column concatenation recalculates in well under a second on modern hardware. The bottleneck is rarely the concatenation itself but rather the upstream lookups, volatile functions like INDIRECT or OFFSET, or formula array dependencies. Profile your workbook with the Formulas, Calculation, Calculate Now toggle if performance becomes an issue, and consider Power Query for any join that runs once per refresh rather than once per keystroke.
To put everything together, here are the practical habits that separate fluent Excel users from beginners when it comes to string concatenation. First, always start by sketching the desired output on paper or in a comment cell. Knowing exactly what the final string should look like, including delimiters and formatting, prevents you from building formulas that almost work but produce subtle errors. Sketching takes 30 seconds and saves hours of trial and error in the formula bar.
Second, build incrementally. Start with a basic =A1&B1 join, confirm the values look right, then add a delimiter, then add TRIM, then add TEXT for numbers, and so on. Each step lets you spot a regression immediately rather than debugging a fifty-character monster formula at the end. Excel's formula bar supports Alt+Enter on Windows and Ctrl+Option+Return on Mac to insert line breaks, which makes long formulas easier to read while you develop them.
Third, use named ranges and the LET function for repeated subexpressions. If your formula calls VLOOKUP three times on the same key, define the lookup once with LET and reference the name in your concatenation. This reduces both formula length and recalculation time. Named ranges defined in Formulas, Name Manager also help by giving meaningful names to your source ranges, so a formula reads like =TEXTJOIN(", ", TRUE, CustomerNames) instead of =TEXTJOIN(", ", TRUE, Sheet2!A2:A500).
Fourth, document any concatenation that took more than two minutes to build. A short note in an adjacent cell explaining what the formula produces and why each part is there will save your future self and your teammates. For especially complex string-building logic, consider moving the work into Power Query or a small helper sheet where each step is visible and named, rather than packing everything into one cell.
Fifth, always test edge cases before declaring victory. Create a test row with a blank cell, a row with leading spaces, a row with a date, a row with a number, and a row with special characters like commas, quotes, or non-ASCII text. Confirm each one produces the expected output. This five-row test bench is a habit that catches 90 percent of production bugs before the workbook ever reaches a colleague or a deadline.
Sixth, learn keyboard shortcuts that speed up concatenation work. F2 enters edit mode on the active cell. F4 toggles absolute references. Ctrl+Shift+Enter applies an array formula in older Excel versions, though dynamic arrays in Microsoft 365 remove that need. Ctrl+; inserts today's date as a static value, useful when you want to concatenate a stamp. Ctrl+Shift+; inserts the current time. These small efficiencies compound over a workday.
Finally, keep a personal cheat sheet of your three or four favorite concatenation patterns. Most professional Excel users converge on a small set of formulas they use over and over: one for full names, one for address blocks, one for CSV lists, one for composite lookup keys. Once your patterns are memorized, building the next formula becomes a thirty-second exercise rather than a five-minute research project. That fluency is the real payoff of learning excel string concat well.