If A2 contains Jane and B2 contains Doe, use =CONCATENATE(A2," ",B2) to join them with a space, producing Jane Doe. In Microsoft Excel for Microsoft 365, you can also use the newer CONCAT function or the ampersand operator; all three combine text, but they handle ranges and compatibility a little differently.
What the CONCATENATE formula does
CONCATENATE joins text values in the order you specify. Its arguments can be cell references, typed text in quotation marks, or a mixture of both. The function does not add spaces, commas, hyphens, or other punctuation on its own: if you want a separator, include it as an argument.
For example, if A2 contains Jane and B2 contains Doe, the formula =CONCATENATE(A2,B2) returns JaneDoe. Adding a quoted space between the references changes the result to Jane Doe. This is a frequent source of confusion: Excel joins exactly what the formula specifies, with no automatic spacing.
The function is useful for assembling labels, names, codes, addresses, and messages from values stored in separate cells. It does not merge cells or alter the source values; it creates a combined result in the cell containing the formula. The original cells remain unchanged, so you can edit them and have the result update automatically.
Syntax and how to add separators
The basic syntax is =CONCATENATE(text1, [text2], ...). The first text argument is required; later arguments are optional. In Excel formulas, text you type directly must be enclosed in straight double quotation marks, while cell references are written without quotation marks. The square brackets in the syntax description indicate optional arguments; do not type those brackets into your formula.
To add a separator, place it between the cell references as a quoted text argument. With the first name in A2 and the last name in B2, this returns Jane Doe: =CONCATENATE(A2," ",B2). For a comma and space between a city in A2 and a state abbreviation in B2, use =CONCATENATE(A2,", ",B2). The result for Seattle in A2 and WA in B2 is Seattle, WA.
Common separators include a single space, a comma followed by a space, a hyphen, a slash, and a line break. To create a line break within a cell on Windows, use the CHAR function with character number 10. With a street address in A2 and a city in B2, =CONCATENATE(A2,CHAR(10),B2) joins the values with a line break. Turn on Home > Wrap Text to display multiple lines inside the cell.
CONCATENATE, CONCAT, or the ampersand?
CONCATENATE is the familiar function name and remains useful when a workbook needs to work with older Excel files. Microsoft 365 also provides CONCAT, which has a shorter name and can accept a range as an argument. The ampersand (&) is an operator that joins values directly and works in Excel without requiring either function.
For two cells, the three approaches can produce the same text. With Jane in A2 and Doe in B2, =CONCATENATE(A2," ",B2), =CONCAT(A2," ",B2), and =A2&" "&B2 each return Jane Doe. The ampersand version is often convenient for a short formula, while functions can make a long list of arguments easier to scan. Choose one style and use it consistently within a workbook.
CONCATENATE does not take a range as a single argument in the same way CONCAT does. If A2:A4 contains red, green, and blue, =CONCAT(A2:A4) returns redgreenblue. It does not put separators between those values. For a range joined with a chosen delimiter, TEXTJOIN is usually clearer.
Join a range with TEXTJOIN
TEXTJOIN combines items using a delimiter you choose and can optionally ignore empty cells. It is available in Microsoft 365 and newer Excel versions. If A2:A4 contains red, a blank cell, and blue, =TEXTJOIN(", ",TRUE,A2:A4) returns red, blue. The first argument is the delimiter, TRUE tells Excel to skip empty cells, and the range is the list to combine.
TEXTJOIN is especially practical for lists of variable length, such as a set of tags or a row of optional address components. If the empty-cell option is FALSE, empty cells are included in the sequence and can leave extra delimiters. If the option is TRUE, blank cells are skipped, but cells containing a formula that returns an empty string may require checking how the source data is constructed.
Use CONCATENATE or the ampersand when you need to position a few values and separators precisely. Use CONCAT to combine a range without a delimiter, and TEXTJOIN when a range needs a repeated separator or blank cells should be handled deliberately.
Combining numbers, dates, and formatted values
When Excel joins a number with text, it converts the number to text, but it may not preserve the number format displayed in the original cell. A date that appears as May 1, 2026 in a worksheet can become a serial number when concatenated directly. The result is text, so it no longer behaves like a date for sorting or date calculations.
To control a date’s appearance, format it with TEXT before joining it. If A2 contains the date May 1, 2026, ="Due: "&TEXT(A2,"mmmm d, yyyy") returns Due: May 1, 2026. The format pattern inside TEXT determines how the date is displayed in the resulting text. The underlying date in A2 is not changed.
Leading zeros need similar care. If A2 contains the number 123, a custom number format might display it as 00123 in the cell, but concatenation may use the underlying value instead. To produce a five-digit text code, use =TEXT(A2,"00000"), which returns 00123 for that input. This is useful for identifiers, but remember that the result is text rather than a number.
For currency or other numeric formats, use TEXT with an appropriate format code if the exact appearance matters. Be cautious when turning values into text: a combined display label is convenient for reports, but calculations should generally continue to use the original numeric cells.
Common problems and how to fix them
- Missing spaces or punctuation: Add the desired separator as a quoted argument or text segment. Excel will not insert one automatically.
- Unexpected numbers in place of dates: Wrap the date reference in TEXT and specify a date format, rather than joining the raw date value.
- Extra separators around blank cells: Use TEXTJOIN with its ignore-empty option set to TRUE, or build a formula that checks whether each optional value is present.
- Formula displayed instead of its result: Check that the cell is not formatted as Text, that the formula begins with an equals sign, and that Show Formulas is not enabled. If the cell was set to Text, change its format to General and re-enter the formula.
- Formula error after copying: Confirm that each cell reference points to the intended row. Relative references such as A2 change when copied down; add dollar signs to lock a reference if it must stay fixed.
- Wrong punctuation in a formula: Some regional settings use semicolons as argument separators instead of commas. If Excel rejects a formula copied from a comma-based example, check the separator convention used by that installation.
A related misconception is that joining text combines the source cells into one cell. It does not: the formula displays a combined value elsewhere, and the original cells stay separate. If you need a static result rather than a live formula, copy the result and use Paste Special > Values. Keep a copy of the original data first, because replacing formulas with values removes their connection to the source cells.
Choosing the right method for your workbook
For a workbook used only in current Excel, CONCAT or TEXTJOIN can make range-oriented tasks easier. CONCATENATE remains understandable and can be preferable when compatibility with older workbooks matters. The ampersand is a concise choice for short formulas, such as joining a first name, a space, and a last name.
Use this quick guide:
- Two or a few pieces of text: Use the ampersand, CONCATENATE, or CONCAT—whichever makes the formula easiest to read.
- A range with no separator: Use CONCAT if your Excel version supports it.
- A range with commas, spaces, or another delimiter: Use TEXTJOIN.
- Numbers or dates where display format matters: Use TEXT to specify the appearance before joining.
- A result that must stay connected to source cells: Keep the formula. For a fixed text result, copy and paste values instead.
For a repeatable report, it can be better to keep data in separate columns and combine it only for display. Separate fields are easier to sort, filter, validate, and reuse. A concatenated label is most useful as an output, not necessarily as the only stored version of the data.
Frequently Asked Questions
What is the formula to combine two cells in Excel?
Use =A2&B2 to join two cells directly, or insert a quoted separator, as in =A2&" "&B2, to add a space. You can also use CONCATENATE or CONCAT.
Does CONCATENATE automatically add a space?
No. Add a space as a text argument between the references, such as =CONCATENATE(A2," ",B2). Without that argument, the values run together.
What is the difference between CONCAT and CONCATENATE?
Both join text. CONCAT is the newer function and can accept a range as an argument; CONCATENATE uses individual text arguments and is retained for compatibility. Neither automatically inserts delimiters.
How do I combine cells with a comma between them?
For two cells, place a comma and space in quotation marks between their references, such as =CONCATENATE(A2,", ",B2). For a range, TEXTJOIN can apply the same delimiter between values.
How do I combine cells and ignore blanks?
Use TEXTJOIN and set its second argument to TRUE. For example, with values in A2:A4, =TEXTJOIN(", ",TRUE,A2:A4) joins nonblank entries with comma-space separators.
Related
- Free Cash Flow Template for Excel: Setup and Use
- Cash Flow Tracker Excel Template: Build and Use It
- Cash Flow Statement Template Excel: Build and Use
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- IFERROR vs ISERROR: Which Excel Function Should You Use?IFERROR catches an error produced by a calculation and returns a replacement value; ISERROR checks whether a value…
- VLOOKUP Errors in Excel: Causes, Checks, and FixesMost VLOOKUP errors come from a lookup value that does not match the first column of the search…
- XLOOKUP Showing Formula Instead of Result?If XLOOKUP displays the formula itself instead of a result, first turn off Show Formulas on the Formulas…
- Excel COUNTIF Dates Within a Month: Formula and TipsTo count dates in a particular month in Excel, use COUNTIFS with a start date and an exclusive…
- Pull Numbers From Text in Excel: Formulas & MethodsYes. In Excel for Microsoft 365, you can pull every digit from a text cell with a formula,…
- Excel Formula to Get a Distinct List (With Examples)In Excel for Microsoft 365, use =UNIQUE(A2:A20) to return a distinct list from the values in A2:A20, with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.