Sooner or later every spreadsheet user needs excel join strings: putting text from two or more cells, or text and numbers, together into one cell. A first name in one column and a surname in another becomes a full name; a product code and a size become a single label. Excel offers several ways to do excel join strings, from the quick ampersand to the modern TEXTJOIN function, and this guide explains each method, when to use it and the traps, especially around dates and numbers, that catch everyone once.
Nothing here needs macros or add-ins. Every method is a plain formula you type into a cell, and the examples use ordinary cell references you can adapt to your own sheet in seconds.
The ampersand: the simplest way to excel join strings
The ampersand symbol (&) is Excel’s original joining operator, and it remains the fastest way to excel join strings for a quick job. The pattern is =A2 & B2, typed with the actual ampersand character between the references, which glues the contents of the two cells together with nothing in between. Because names and labels usually need a gap, you add literal text in quotation marks: =A2 & ” ” & B2 joins a first name, a space and a surname. You can chain as many pieces as you like, mixing cell references and typed text freely, which makes the ampersand ideal for building sentences such as =”Order ” & A2 & ” is ready”.
Two habits make ampersand formulas painless. First, remember that numbers join as plain values, which is usually what you want for codes and counts. Second, check your spaces: the most common excel join strings mistake is a missing or doubled space where two cells meet, and it is always the formula’s fault, because Excel joins exactly what you tell it to and not one character more. Read the result once before you copy the formula down a thousand rows.
CONCATENATE, CONCAT and TEXTJOIN: the functions that excel join strings
Excel has three functions for the job, built in different decades. CONCATENATE is the veteran: =CONCATENATE(A2, ” “, B2) does what the ampersand does, listing the pieces as arguments. It still works everywhere, including very old files, which is its main virtue. CONCAT, introduced in newer versions, replaces it and adds one real improvement: it accepts a whole range, so =CONCAT(A2:C2) joins a row of cells in one step. TEXTJOIN is the modern answer and the one to learn properly, because it solves the two chores the others make tedious.
TEXTJOIN takes a delimiter, the text placed between every pair of pieces, a choice about ignoring empty cells, and then the cells themselves. =TEXTJOIN(” “, TRUE, A2, B2) joins with spaces and skips blanks automatically. For anyone who must excel join strings from columns with gaps, addresses, tags, lists of names, TEXTJOIN turns a nest of IF functions into one readable line. The table below compares the four methods so you can pick by situation rather than by habit.
| Method | Best for | Watch out for |
|---|---|---|
| Ampersand operator | Quick joins of a few cells with custom text between them | Long formulas get hard to read; spaces are your job |
| CONCATENATE | Compatibility with old versions and shared files | No ranges; every cell listed separately |
| CONCAT | Joining a whole range of cells at once | No delimiter or empty-cell control |
| TEXTJOIN | Lists with a repeated separator and possible gaps | Only in newer Excel versions and Microsoft 365 |
Joining dates and numbers without ugly results
The trap waiting inside excel join strings involves formatted numbers. When you join a cell holding a date, Excel inserts the date’s underlying serial number, a long integer, not the friendly format you see on screen. Currency and percentages misbehave similarly, dropping their symbols and sometimes sprouting long decimal tails from percentages. The fix is the TEXT function, which formats a value inside the formula: =”Due ” & TEXT(A2, “dd mmm yyyy”) produces “Due 05 Jan 2026” style results, and TEXT(B2, “0.00”) keeps a price at two decimal places. Any serious excel join strings work with dates, money or measurements needs TEXT riding along, and discovering this early saves a memorable afternoon.
A second trap is that joined results are text, not values. A label built from a number will not add, sort as a number or match in lookups expecting numbers. Keep your source columns intact, put joins in a new column, and only convert results to static values, with copy and paste values, when the job is finished and you are sure you will never need the pieces separately again.
Putting it together: the jobs people actually do
The daily jobs behind excel join strings are wonderfully ordinary. Mail merges and CRM imports want full names from split columns. Warehouse and shop sheets want a single SKU from code, colour and size parts. Teachers build class lists; event organisers build badge labels from title, name and organisation; analysts build readable keys for matching data between systems by joining an account code to a date. In each case the pattern is the same: decide the separator, usually a space, a dash or a comma; choose TEXTJOIN if the source has gaps, the ampersand if the pattern is short and irregular; wrap dates and money in TEXT; and proof-read row one before filling down.
One last technique completes the toolkit: joining with line breaks. Adding CHAR(10) between pieces, and switching on text wrapping for the cell, stacks an address or a two-line label inside one cell. It is the same excel join strings machinery, with the separator set to an invisible character instead of a space, and it is the standard way to build tidy multi-line labels without merging cells.
A no-formula alternative: Flash Fill and Power Query
Two tools deserve mention for people who join the same pattern hundreds of times and would rather not maintain formulas. Flash Fill watches you type the first one or two finished examples in a new column, detects the pattern, names stitched together, codes merged with sizes, and offers to complete the whole column in one keystroke. It is astonishingly good at exactly the jobs this article covers, and because it writes plain values rather than formulas, the results are stable and portable. Its weakness is the mirror of its strength: with no formula to inspect, an undetected mistake in the pattern is harder to trace, so check the far end of the column as well as the top before you trust it.
Power Query, Excel’s data-shaping tool, is the heavy machinery for joins that must be repeated on fresh data every week or month: merging columns there becomes a recorded step that reapplies itself to each new file. For a one-off list, a formula is faster; for a monthly report assembled from the same exports, recording the join once is the professional answer. Choosing between formula, Flash Fill and Power Query is mostly a question of whether the job will ever need doing again, and being honest about that answer saves more time than any single function.
Common errors and how to spot them
Three errors account for nearly every broken join. The first is the missing quotation marks around typed text, which produces a name error because Excel reads the bare word as a reference to something. The second is the phantom space, either absent between names or doubled where a source cell already ends with one; sorting the result alphabetically makes doubled spaces visible because the ordering looks subtly wrong. The third is joining cells that contain formulas returning empty text, which leaves stray separators wandering through the result; TEXTJOIN with its ignore-empty option set to TRUE is the one-line cure. Proof-reading the first, middle and last rows of any filled column catches all three, and takes under a minute.
Frequently asked questions
What is the easiest way to excel join strings? Use the ampersand: =A2 & ” ” & B2 joins two cells with a space between them. For anything longer or gap-prone, TEXTJOIN is easier to read and maintain, and it is the method most guides now teach first.
Why does my joined date show as a big number? Because dates are stored as serial numbers and joining uses the raw value. Wrap the date in TEXT with a format code, such as TEXT(A2, “dd mmm yyyy”), whenever you excel join strings that include dates, money or percentages.
What is the difference between CONCAT and TEXTJOIN? CONCAT joins cells and ranges but adds nothing between the pieces and cannot skip blanks. TEXTJOIN adds a chosen delimiter between every piece and can ignore empty cells, which makes it far better for lists, tags and addresses.
Can I join text from different rows instead of columns? Yes. Any of the methods accept any cells, in any order, from anywhere in the workbook. TEXTJOIN with a range down a column, plus the fill handle used carefully, covers most reporting jobs where excel join strings run vertically.
Conclusion
To excel join strings is to master one small family of tools: the ampersand for speed, CONCATENATE for compatibility, CONCAT for ranges and TEXTJOIN for anything with separators and gaps, with TEXT standing guard over your dates and numbers. Pick the method by the job, mind your spaces, and a whole class of spreadsheet drudgery disappears.
Neighbouring skills worth having: our guide to combining text from two cells in Excel gives extra worked examples, combining two text cells in Excel covers the quick methods, and our explainer on the cell address in Excel firms up the references every formula depends on.
