Excel’s CONCAT function joins text from cells or text you type into a formula; it does not add spaces or punctuation unless you include them. With a first name in A2 and a last name in B2, =CONCAT(A2," ",B2) returns Ada Lovelace if those cells contain Ada and Lovelace.
What CONCAT does
CONCAT combines text into one text string. Its arguments can be cell references, ranges, or text enclosed in double quotation marks. The function is useful for building full names, labels, codes, addresses, and other text from information stored in separate cells. It changes the formula result, not the original source cells.
CONCAT does not automatically insert a separator between its arguments. If A2 contains Ada and B2 contains Lovelace, joining only those two references produces AdaLovelace. To get Ada Lovelace, supply a space as an additional argument. Punctuation works the same way: include a comma, hyphen, or other character as quoted text wherever it should appear.
CONCAT is available in current Microsoft Excel, including Microsoft 365. It is also supported in recent perpetual versions, such as Excel 2016 and later. A workbook opened in an older Excel release that does not support CONCAT may display a name error; CONCATENATE is the older function that can serve as a substitute in many simple formulas.
Basic CONCAT formula and examples
To create a three-part name, assume A2 contains Ada, B2 contains Byron, and C2 contains Lovelace. This formula returns Ada Byron Lovelace:
=CONCAT(A2," ",B2," ",C2)
Arguments are read from left to right, so the order in the formula determines the order in the result. You can mix cell references with fixed text. For instance, to create a label that reads Name: Ada Lovelace, assume A2 contains Ada and B2 contains Lovelace. The result is Name: Ada Lovelace:
=CONCAT("Name: ",A2," ",B2)
Text that you type directly into a formula must be surrounded by straight double quotation marks. Cell references and function names are not quoted. If you need a quotation mark within the finished text, Excel’s formula syntax requires doubling that character inside the quoted text; for everyday joining, however, a space or punctuation mark is usually all that is needed.
After entering a formula in a worksheet cell, press Enter to calculate it. To use the same pattern for other rows, copy or fill the formula down. Relative references such as A2 and B2 adjust to A3 and B3 in the next row, while absolute references remain fixed if you use dollar signs.
Add spaces, punctuation, and other separators
Separators are ordinary text arguments. A comma followed by a space is often useful for displaying a last name and first name, while a hyphen can join parts of a product code. Put each separator in the position where it belongs in the finished string.
Assume A2 contains Lovelace and B2 contains Ada. The following formula returns Lovelace, Ada:
=CONCAT(A2,", ",B2)
For an address, assume A2 contains 25, B2 contains Oak Street, and C2 contains Springfield. This formula returns 25 Oak Street, Springfield:
=CONCAT(A2," ",B2,", ",C2)
Think through the exact output before writing a longer formula. If each piece already includes a trailing or leading space, adding another space argument can create doubled spaces. Conversely, omitting a separator can make the result hard to read. A quick check of the source cells and the visible result usually reveals these issues.
Join a range of cells
CONCAT can accept a range reference as an argument, which can be quicker than listing many individual cells. It combines the range’s values in their worksheet order, but still inserts no delimiter. Assume A2 contains E, A3 contains F, and A4 contains G. The formula returns EFG:
=CONCAT(A2:A4)
This behavior matters when the result is meant to be readable. If A2:A4 contain three words, a direct range join will run the words together unless the cells themselves contain spaces. CONCAT also returns one combined result; it does not create a separate joined result for every row or automatically spill a list of results down the worksheet.
When joining a range with a consistent separator, TEXTJOIN is usually more convenient. Assume A2:A4 contain E, F, and G. This formula returns E, F, G, with a comma and space between each item:
=TEXTJOIN(", ",TRUE,A2:A4)
The second argument tells TEXTJOIN whether to ignore empty cells. Here, TRUE means empty cells are skipped. That can be helpful for optional middle names or incomplete address fields, where a fixed CONCAT formula might leave extra separators. TEXTJOIN is available in Microsoft 365 and modern Excel versions; for a workbook intended for older software, check that the recipient’s version supports it.
Keep numbers, dates, and leading zeros readable
When CONCAT joins a number, it uses the value rather than necessarily reproducing the way that value appears in its source cell. A date stored as a serial number, for example, may not look like a formatted date in the combined result. Cell formatting alone does not reliably control how a value appears inside concatenated text. Use TEXT to specify the display format as part of the formula.
Assume A2 contains the label Report and B2 contains the Excel date March 5, 2026. This formula returns Report — Mar 5, 2026:
=CONCAT(A2," — ",TEXT(B2,"mmm d, yyyy"))
The format code inside TEXT determines the date’s appearance: mmm gives an abbreviated month name, d gives the day number, and yyyy gives a four-digit year. Other common date formats include a numeric month and day or a full month name. The date must be a real Excel date value for TEXT to format it as a date; text that merely looks like a date may need cleaning or conversion first.
Leading zeros are another common concern. If A2 contains the number 27, but the desired code has five digits, assume that the required result is 00027. Formatting that number as text before joining preserves the zeros:
=CONCAT(TEXT(A2,"00000"),"-X")
This returns 00027-X. The zeros in the format pattern are placeholders that require digits in those positions. This is useful for display codes, but it does not change the underlying number in A2. If an identifier is not meant for arithmetic, storing it as text from the start can also be a sensible choice.
CONCAT, ampersand, and TEXTJOIN compared
CONCAT is a function-based way to join text. The ampersand (&) is Excel’s concatenation operator and can produce the same result in short formulas. Assume A2 contains Ada and B2 contains Lovelace. This formula also returns Ada Lovelace:
=A2&" "&B2
For a couple of cells, either method is straightforward. CONCAT may be easier to scan when you want a named function or are passing a range; ampersands can feel compact for a short expression. Choose the style that makes the formula easiest for you and other workbook users to understand.
TEXTJOIN is the better fit when many items need the same delimiter, especially when blank cells should be ignored. CONCAT is useful when the pieces need individually placed separators or when no separator is wanted. CONCATENATE is the older function name; Microsoft recommends CONCAT as its replacement, and CONCAT can work with range references in a way that makes some range-based tasks less cumbersome.
- Use CONCAT to join text and ranges without an automatic delimiter.
- Use TEXTJOIN to apply one delimiter between items and optionally skip blanks.
- Use the ampersand operator for a short, direct join between a few values.
- Use TEXT inside a join when numbers or dates need a specific displayed format.
Common problems and how to fix them
If the result has no spaces, check whether the formula includes a quoted space between the references. CONCAT does not add one for you. If the result contains too many spaces or punctuation marks, check whether the source cells already include those characters. Cleaning the source data may be better than adding increasingly complicated fixes to the formula.
If a date appears as a number, format it with TEXT as shown above. If a product code loses its leading zeros, remember that numeric cell formatting is not the same as converting the value to formatted text inside the formula. TEXT can supply a fixed-width pattern, while storing an identifier as text can prevent Excel from treating it as a quantity.
Blank cells usually contribute no characters to a CONCAT result, but the separators you explicitly include remain. Thus, a formula designed to join first, middle, and last names with spaces can leave an awkward gap when the middle-name cell is blank. For a variable-length list, TEXTJOIN with its ignore-empty option is often a cleaner solution.
Excel may also reject a formula if the argument separators do not match the computer’s regional settings. The examples here use commas, as in standard English-language formula syntax. Some regional configurations use semicolons between function arguments; if Excel marks commas as an error, check the list-separator setting or use the separator expected by that installation. Finally, a single Excel cell cannot hold more than 32,767 characters, so an unusually large combined result can exceed the worksheet’s cell limit.
Frequently Asked Questions
Does CONCAT add a space between cells?
No. CONCAT joins the supplied values directly. Include a quoted space as an argument wherever a space is needed, or use TEXTJOIN when one delimiter should go between a range of items.
Can CONCAT combine text and numbers?
Yes. Excel can convert numeric values to text as it builds the result. If the number must have a specific appearance, such as a date format or leading zeros, use TEXT to define that appearance before joining it.
How do I combine first and last names in Excel?
Place the first and last names in separate cells, then join their references with a quoted space between them. Check the source data for existing spaces, and consider how blank name fields should appear.
What is the difference between CONCAT and TEXTJOIN?
CONCAT joins values without inserting a separator automatically. TEXTJOIN lets you specify a delimiter for the items and choose whether empty cells are ignored, making it convenient for lists.
Why does my CONCAT formula show a date as a number?
Dates are stored as numeric values in Excel. To show a readable date in the combined text, wrap the date reference in TEXT and provide a date format code.
Can CONCAT join a whole column?
It can accept a range reference, but joining a very large range may create an unwieldy result and can reach Excel’s 32,767-character cell limit. For most tasks, select only the needed range; use TEXTJOIN if you also need separators or blank-cell handling.
Related
- Excel Budget Spreadsheet Free? Find Templates and Tips
- Excel Accounting Format: How It Works and When to Use It Properly
- Excel Accounting Format: Show Negative Numbers in Brackets
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUM: How to Add Numbers with a FormulaTo add numbers in Excel, select the cell where you want the total, type a SUM formula, and…
- IRR Function in Excel: Calculate an Investment ReturnUse Excel’s IRR function to estimate the rate of return for a series of cash flows that occur…
- XLOOKUP in Excel: Practical Lookup Examples, Tips, and UsesExamples of XLOOKUP in Excel include finding a customer name from an ID, returning the latest matching price,…
- IFERROR in Excel: Syntax, Examples, and Common FixesIn Excel, IFERROR checks whether a calculation returns an error and displays a value you choose instead; its…
- Excel MATCH Function: Arguments and Match TypesThe Excel MATCH function takes a value to find, a one-dimensional range to search, and an optional match…
- Excel NPV of a Series of Cash Flows: Formula and ExamplesTo calculate the net present value of a cash-flow series in Excel, use =NPV(B1,B3:B5)+B2 when the discount rate…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.