In Excel, CONCATENATE joins text from multiple cells or typed values into one string. For example, with a first name in A2 and a last name in B2, enter =CONCATENATE(A2," ",B2) in another cell to combine them with a space; in current Excel, you can also use the ampersand operator, CONCAT, or TEXTJOIN.
What CONCATENATE means in Excel
Concatenation is the process of linking text values together in sequence. CONCATENATE is the name of Excel’s older, built-in function for doing this. It is still supported in Microsoft 365 for Windows, so existing workbooks that use it continue to calculate. Microsoft’s newer CONCAT function offers a more convenient way to combine ranges, while TEXTJOIN can add a chosen separator and skip empty cells.
Joining values does not merge the source cells or change their contents. The formula creates a result in the cell where you enter it. If A2 contains Maya and B2 contains Chen, for example, the result might be Maya Chen, while the two original cells remain separate. If either source value changes, Excel updates the result.
Concatenation is useful for assembling full names, addresses, labels, descriptions, file names, and readable messages from fields stored separately. It can also prepare text for export or create a consistent identifier from several columns. It is not the same as adding numbers: joining 12 and 34 produces the text 1234, not the numeric sum 46.
Basic syntax and how the function works
The function’s general pattern is CONCATENATE(text1, [text2], …). The first argument is required; later arguments are optional. An argument can be a cell reference, a number, or text typed directly into the formula. Text typed directly must be enclosed in straight double quotation marks. Arguments are joined in the order they appear, and Excel does not insert spaces, commas, or other punctuation automatically.
With the words North and Harbor in A2 and B2, respectively, this formula returns NorthHarbor because it contains no separator: =CONCATENATE(A2,B2). With the same cell layout, this version returns North Harbor: =CONCATENATE(A2," ",B2). The space is a separate text argument between the two cell references.
In formulas, punctuation and spaces are values too. Type them inside quotation marks wherever they should appear. You can use a comma and a space, a hyphen, a slash, or a label such as a colon. Excel uses commas to separate function arguments in the examples here; depending on regional settings, a particular installation may use semicolons as its formula separator.
Practical CONCATENATE examples
With a title in A2, a first name in B2, and a last name in C2, this formula returns Dr. Maya Chen: =CONCATENATE(A2," ",B2," ",C2). The formula alternates cell references and literal spaces to control the result. If the title is sometimes blank, the formula may leave extra spaces; TEXTJOIN can be a better fit when optional parts are common.
With a product code in A2 and a size in B2, this formula returns ITEM-482 / Large: =CONCATENATE("ITEM-",A2," / ",B2). The fixed words and punctuation are text arguments, while A2 and B2 supply changing values. This pattern is handy for building a readable label from a code and a description.
With the text Invoice in A2 and a real Excel date in B2 representing January 2, 2025, this formula returns Invoice — Jan 2, 2025: =CONCATENATE(A2," — ",TEXT(B2,"mmm d, yyyy")). TEXT applies the desired display format before the date is joined. Without TEXT, Excel may concatenate the date’s underlying serial number rather than the familiar date display shown in the cell.
With a five-digit postal code stored as a number in A2 and a city in B2, this formula displays the code with leading zeroes and then the city: =TEXT(A2,"00000")&" "&B2. For example, if A2 contains 123 and B2 contains Boston, the result is 00123 Boston. This matters because numeric values do not retain leading zeroes as part of their value. If postal codes can begin with zero, storing them as text is often safer.
Joining a range with CONCAT or TEXTJOIN
CONCATENATE is convenient when you know the individual cells to join, but a long list of cell references makes a formula cumbersome. CONCAT is the newer function for joining text and can take a range reference. With A2:A4 containing Red, Green, and Blue, this formula returns RedGreenBlue: =CONCAT(A2:A4). Like CONCATENATE, CONCAT does not add a delimiter between the values.
TEXTJOIN is useful when values need a separator. With A2:A4 containing Red, Green, and Blue, this formula returns Red, Green, Blue: =TEXTJOIN(", ",TRUE,A2:A4). The first argument is the delimiter, and TRUE tells Excel to ignore empty cells. If the second argument is FALSE, empty cells are not ignored; separators may therefore appear for blank entries. TEXTJOIN is available in Microsoft 365 and recent perpetual Excel versions, including Excel 2019, 2021, and 2024.
For a single row of optional address details, TEXTJOIN can avoid repeated separators. With A2:C2 containing a street address, an optional apartment number, and a city, this formula joins the nonblank cells using a comma and a space: =TEXTJOIN(", ",TRUE,A2:C2). If B2 is blank, Excel skips it rather than placing two adjacent commas. Check that the cells are arranged in the order you want before using a range.
Choosing between CONCATENATE, CONCAT, TEXTJOIN, and &
These methods produce text, but they suit different tasks. Choose based on how many values you are joining, whether you need a separator, and whether blank cells should be skipped.
- CONCATENATE: A clear choice for a few known cells or for maintaining an older workbook. It requires you to add any spaces or punctuation as arguments.
- CONCAT: Useful for joining a range or several individual values without a delimiter. It is the more modern function for this basic task.
- TEXTJOIN: Best when you want a consistent delimiter, such as a comma and space, and need an option to ignore empty cells.
- The ampersand (&): A compact operator for short formulas. It is often easy to read when joining only two or three pieces of text.
With first name Maya in A2 and last name Chen in B2, this ampersand formula returns Maya Chen: =A2&" "&B2. It performs the same basic join as the equivalent CONCATENATE formula, but uses the & operator instead of a function. There is no general need to replace a working CONCATENATE formula solely because newer options exist. For new formulas, pick the method that makes the intended separator and treatment of blanks easiest to understand.
Formatting numbers, dates, and line breaks
When Excel joins a numeric value, it uses the value rather than necessarily preserving the cell’s visible number format. A percentage, date, or number with leading zeroes may therefore appear differently in the combined result. Use TEXT when the output needs a specific format. For example, if A2 contains 0.25 and should appear as 25%, this formula returns Rate: 25%: ="Rate: "&TEXT(A2,"0%").
You can also combine details on separate lines, which is useful for a compact address or contact block. With A2:C2 containing a name, street, and city, this formula joins them with line breaks and skips blank cells: =TEXTJOIN(CHAR(10),TRUE,A2:C2). Turn on Home > Alignment > Wrap Text for the result cell so the line breaks display within the cell. The formula does not change the source cells, and row height may need adjusting to show every line.
For a fixed two-part combination, CHAR(10) can also be placed between references with CONCATENATE or ampersands. The delimiter still has to be supplied explicitly. A line break may not display as expected if Wrap Text is off, so check the cell’s alignment settings before assuming the formula failed.
Common problems and how to fix them
- Missing spaces: Excel does not add spaces automatically. Include a quoted space between the references, or use a delimiter-based TEXTJOIN formula.
- Unexpected date or number: Apply the intended display format with TEXT before joining the value. Cell formatting alone may not carry into the combined text.
- Extra punctuation around blanks: CONCATENATE and CONCAT still join the literal separators you give them. Use TEXTJOIN with its ignore-empty option when blank entries should disappear along with their separators.
- Formula displayed instead of a result: Check that the cell is not formatted as Text, and that the formula begins with an equals sign. If the cell was already set to Text, change its format to General and re-enter the formula.
- Unexpected characters or spaces: Source cells may contain leading, trailing, or nonbreaking spaces. Inspect the source data and clean it where appropriate before joining; a separator in the formula cannot remove hidden characters already in a cell.
Excel cells can contain at most 32,767 characters. A formula that tries to create a result longer than the cell limit cannot return a complete value. For ordinary labels and names this is rarely an issue, but it can matter when combining long descriptions or large text fields. Also remember that a concatenated result is text: if you later need to calculate with a number inside it, use the original numeric cells rather than the finished label.
A reliable way to build and check a formula
For a formula that will be reused in a workbook, make the layout and output predictable before filling it down:
- Identify the source cells and decide the exact order of the pieces.
- Write down required separators, including spaces, punctuation, or line breaks.
- Decide whether blank cells should leave a gap or be skipped. That choice often determines whether CONCATENATE, CONCAT, or TEXTJOIN is the best tool.
- Format numbers and dates inside the formula with TEXT if the combined result must show a specific appearance.
- Test the formula with a typical row and a row containing a blank or unusual value, then copy it to other rows if the result is correct.
When copying a formula down a column, relative references such as A2 and B2 normally adjust to A3 and B3 in the next row. If a particular reference must stay fixed, add dollar signs to make it absolute, such as $A$1. Treat the finished result as a display or export string, not as a replacement for the original source columns; retaining those columns makes later sorting, editing, and calculations easier.
Frequently Asked Questions
Is CONCATENATE still available in Excel?
Yes. CONCATENATE remains supported in current Excel, including Microsoft 365 for Windows. CONCAT is the newer alternative, but existing formulas using CONCATENATE do not need to be rewritten just because the function is older.
What is the difference between CONCATENATE and CONCAT?
Both join text. CONCAT is designed to work more conveniently with ranges, while CONCATENATE is typically written with individual text arguments. Neither adds spaces or punctuation automatically, and neither provides TEXTJOIN’s delimiter-and-ignore-blanks options.
How do I combine cells with a space between them?
Place a quoted space between the cell references in the formula. For example, with a first name in A2 and a last name in B2, =CONCATENATE(A2," ",B2) returns the two values separated by one space.
Can CONCATENATE combine cells from different worksheets?
Yes. Use a worksheet-qualified cell reference as an argument, such as Sheet2!A2, and include the other references and separators in the usual order. If the sheet name contains spaces, enclose the sheet name in single quotation marks in the reference, as in ‘Sales Data’!A2.
How can I join cells while ignoring blanks?
Use TEXTJOIN and set its second argument to TRUE. For example, with values in A2:A10, =TEXTJOIN(", ",TRUE,A2:A10) joins nonblank entries with a comma and a space. This is more convenient than adding a separate conditional test for every cell.
Related
- Print Excel Sheets With Gridlines: Steps and Tips
- Can You Protect Cells in Excel? A Practical Guide
- Print an Excel Spreadsheet: Setup, Pages, and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Concatenate in Excel and Add a DashTo join two cell values with a dash in Excel, enter =A2&"-"&B2 in a result cell, replacing A2…
- How to Filter by Color in ExcelTo filter by color in Excel for Microsoft 365 on Windows, turn on filters for your data, open…
- Excel ROUND Function: Examples, Rules, and Common ErrorsTo round a number in Excel, use ROUND: with 12.345 in cell A2, enter =ROUND(A2,2) in another cell…
- VLOOKUP in Excel for Multiple Columns: Methods and TipsYes. In Excel for Microsoft 365, VLOOKUP can return values from multiple columns either by copying one formula…
- Excel Multiply If Not Zero: Formula & GuideTo multiply only when a cell is not zero in Excel, use the IF function: =IF(A2=0,0,A2*B2) or, if…
- Hide Duplicate Rows in Excel with a Filter Step by StepTo hide exact duplicate rows in Excel without deleting them, select the full data range, then choose Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.