Yes. Excel’s CONCAT function can join numbers with text or other values, but it does not preserve a cell’s display formatting automatically. With a product name in A2 and a quantity in B2, for example, use =CONCAT(A2," - ",B2," units") to make a label such as “Pencils – 12 units.” Use TEXT inside the formula when a number must appear as currency, a date, a percentage, or with leading zeros.
What CONCAT does in Excel
CONCAT combines text from multiple values, cells, or ranges into one text string. It is useful for labels, identifiers, addresses, and sentences assembled from worksheet data. It does not add spaces, commas, or other separators unless you include those characters as arguments. A space inside quotation marks is still an argument that you must supply deliberately.
For a simple example, with a first name in A2 and a surname in B2, =CONCAT(A2," ",B2) produces “Mina Patel” if those cells contain Mina and Patel. The result is a single text value in the formula cell; the source cells remain unchanged. CONCAT is available in Microsoft 365 and Excel 2019 or later. In Excel 2016 and earlier, use the ampersand operator or, where available, the older CONCATENATE function.
CONCAT can accept individual values and cell ranges, and it can combine up to 253 text arguments. It does not have a built-in delimiter option, so a formula with many pieces can become hard to read. For lists that need a repeated separator, TEXTJOIN is often a better fit.
How Excel handles numbers in a concatenation
When CONCAT joins a numeric cell, Excel uses the value rather than copying the way the cell looks on the worksheet. A cell formatted as currency may display a dollar sign and two decimal places, but those visual choices are not automatically included in the resulting text. The same issue affects percentages, dates, and numbers padded with zeros.
With a price of 1250.5 in B2, for example, concatenating the cell after a dollar sign can produce a plain value rather than the displayed currency style. With a percentage value of 0.15, the joined text may contain 0.15 rather than 15%. Excel stores dates as serial numbers, too, so a date concatenated without conversion may appear as a number rather than a readable calendar date.
This distinction explains a common surprise: changing a source cell’s number format may change how that cell appears, but it does not reliably give CONCAT a formatted string to copy. If the exact appearance matters, format the value as text inside the formula with TEXT. If the value is already stored as text, CONCAT joins that text as written, including any leading zeros that are actually part of the text.
Also remember that CONCAT produces text, not a numeric total. Joining 12 and 3 gives a string such as “123” if no separator is supplied; it does not add the numbers to make 15. Use arithmetic operators or a function such as SUM when the goal is calculation.
Format numbers with TEXT before joining
The TEXT function converts a numeric value into text using a number format code. Put TEXT around the numeric reference within CONCAT, then include the desired format code in quotation marks. This is the dependable way to keep currency symbols, decimal places, percentages, date styles, or fixed-width digits in the final string.
With a product name in A2 and a price of 1250.5 in B2, this formula creates a label with two decimal places and a dollar sign: =CONCAT(A2," costs ",TEXT(B2,"$#,##0.00")). The result for “Notebook” and 1250.5 is “Notebook costs $1,250.50.” The format code uses a comma for thousands grouping and two zeroes after the decimal point.
With an event name in A2 and a date value in B2, use =CONCAT(A2," on ",TEXT(B2,"mmm d, yyyy")) to produce a result like “Review on Nov 2, 2026.” The date format code controls the text regardless of whether the source cell is displayed in a short-date or long-date style.
With an account number in A2 whose numeric value is 417, use =TEXT(A2,"00000") to return the text “00417.” To combine it with a prefix, use =CONCAT("ID-",TEXT(A2,"00000")), which returns “ID-00417.” If the zeros were lost when the number was first entered, the format code can add them back for display in the text result.
TEXT’s output is text. That is ideal for a sentence or code, but it is not a replacement for the original numeric value if later calculations are needed. Keep the original number in its own cell and build a separate display label when both numeric analysis and formatted wording matter. Format codes can also depend on regional conventions, such as decimal and thousands separators, so check the output if a workbook will be used under different regional settings.
Add spaces, punctuation, and other separators
CONCAT places its arguments next to one another exactly as supplied. Add separators as quoted text arguments. For a city in A2, a comma and space, and a state abbreviation in B2, =CONCAT(A2,", ",B2) returns “Austin, TX.” For a reference number with a hyphen between a year in A2 and a sequence number in B2, =CONCAT(A2,"-",B2) inserts that hyphen.
Do not assume Excel will insert a separator between cells in a range. If A2 contains “North,” B2 contains “East,” and C2 contains “West,” then, with those values in A2:C2, =CONCAT(A2:C2) returns “NorthEastWest.” It does not add spaces or skip a value merely because you expected a list-style join. If a separator should appear between every item, use TEXTJOIN instead.
Be intentional about punctuation in labels. A formula may need a space before a unit, a comma between address fields, or a colon after a heading. If an optional cell is blank, a separator you added as a separate argument can remain, leaving an awkward extra space or punctuation mark. For data with optional parts, consider building the text from nonblank cells with TEXTJOIN or using an IF condition to control whether the separator appears.
Join a range or create a list
CONCAT is convenient when you want all values in a range directly adjacent to one another. With A2:A4 containing “A,” “B,” and “C,” =CONCAT(A2:A4) returns “ABC.” Blank cells contribute no visible text, but CONCAT does not interpret the blanks as a cue to insert a delimiter. This makes it useful for compact strings, but less suitable for readable lists.
For a comma-separated list that ignores empty cells, use TEXTJOIN. With A2:A5 containing “Ana,” a blank cell, “Bo,” and “Chen,” the formula =TEXTJOIN(", ",TRUE,A2:A5) returns “Ana, Bo, Chen.” The first argument is the delimiter, the second tells Excel to ignore empty cells, and the range supplies the items. If you want blanks included in the join, set that argument to FALSE instead.
TEXTJOIN is available in Microsoft 365 and newer Excel versions, including Excel 2019 and later. It is often easier to maintain than a long CONCAT formula when joining a list, because changing the delimiter takes only one edit. For a small fixed label with a few different pieces, CONCAT may be clearer.
CONCAT, the ampersand, and CONCATENATE
The ampersand (&) is a straightforward alternative for joining values. With a first name in A2 and a surname in B2, =A2&" "&B2 returns the same kind of result as =CONCAT(A2," ",B2). Many people prefer & for short formulas because it is compact; CONCAT can be easier to scan when there are several arguments or ranges.
CONCATENATE is the older function name. Microsoft introduced CONCAT as its replacement, with support for ranges that make some joins more convenient. Existing workbooks using CONCATENATE can continue to be useful, but new formulas generally have no reason to use the older name when CONCAT is available. The ampersand remains a simple option when compatibility with older Excel versions is important.
TEXTJOIN fills a different need: it accepts a delimiter and an option to ignore empty cells. It is usually the better choice for a list of names, tags, or address components separated in a consistent way. None of these joining approaches adds numbers mathematically. Choose SUM or ordinary arithmetic for calculation, and CONCAT, TEXTJOIN, or & for creating text.
Common problems and how to fix them
- The result has no spaces. CONCAT does not add them automatically. Add a quoted space, such as
" ", between the relevant arguments. - A date appears as a serial number. Wrap the date reference in TEXT and specify the format you want, for example
TEXT(B2,"mmm d, yyyy"). - Currency or a percent sign disappears. Apply a suitable TEXT format code to the number before joining it; cell formatting alone is not enough.
- Leading zeros are missing. If the identifier is numeric, use a zero-padding format such as
TEXT(A2,"00000"). If it is an identifier rather than a quantity, storing it as text from the start can also prevent zeros being discarded. - The output looks like one number. Check whether you need a separator. Joining 12 and 3 without one creates “123,” not a calculation.
- The formula displays as text or does not calculate. Check that it begins with an equals sign and that the cell is not formatted as Text. If it is, change the cell format to General, then re-enter the formula.
- Excel reports an unrecognized function. CONCAT is not available in some older Excel releases. Use & or CONCATENATE there, or open the workbook in a version that supports CONCAT.
In installations with regional settings that use semicolons as formula separators, Excel may expect semicolons instead of commas between arguments. That is a separator setting, not a different CONCAT feature. If you copy a formula from a guide and Excel rejects it, check the argument separator used by your installation.
Practical choices for everyday worksheets
For a short message made from a name and an amount, CONCAT plus TEXT is a useful pattern. For a date-based label, format the date with TEXT rather than relying on the date cell’s appearance. For an ID with a fixed number of digits, explicitly pad the numeric value or store the identifier as text. For a list from many cells, choose TEXTJOIN if a repeated delimiter or blank-cell handling is needed.
It is also helpful to separate data from presentation. Keep a date as a genuine date and a price as a genuine number in their original cells; create a separate formula for the label that will be printed, exported, or displayed. That preserves the ability to sort dates chronologically, calculate totals, and apply different display formats later. A joined string is best treated as an output for people or systems that need text, not as a replacement for the underlying structured data.
When copying a joining formula down a column, ordinary relative references such as A2 and B2 adjust to A3 and B3 on the next row. If a format code or label comes from a fixed cell, use an absolute reference for that cell where appropriate. After filling the formula down, inspect a few rows with blanks, unusually large values, or dates; edge cases often reveal missing separators or a format code that needs adjustment.
Frequently Asked Questions
Can CONCAT join numbers and text in Excel?
Yes. CONCAT can combine text and numeric cell values in one result. Excel converts the number for the text result, but it does not automatically copy the source cell’s currency, date, percentage, or custom number formatting.
How do I concatenate a number without losing its formatting?
Use TEXT around the number and specify a format code, then join that result with CONCAT. For example, format a price as currency with a code such as "$#,##0.00". The result is text, so retain the original number for calculations.
Does CONCAT add spaces between cells?
No. Add a quoted space as an argument, such as " ", wherever you want one. For the same delimiter between many values, TEXTJOIN is more convenient.
What is the difference between CONCAT and TEXTJOIN?
CONCAT joins its arguments without a delimiter setting. TEXTJOIN lets you specify a delimiter and choose whether to ignore empty cells, making it better for comma-separated or similarly formatted lists.
Can CONCAT add numbers together?
No. CONCAT combines values as text; it does not perform arithmetic. Use + or a function such as SUM to calculate a total. Joining 12 and 3 is a text operation, while adding them produces the numeric result 15.
Related
- Cash Flow Format in Excel: Build a Simple Statement
- CAGR Return in Excel: Calculate Investment Growth
- How to Calculate CAGR in Excel for 3 Years
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel INDEX Function Examples: Useful Formulas and UsesExcel’s INDEX function returns a value—or a reference to a value—from a range based on its row and…
- Excel Date Subtraction: Formulas, Days, and WorkdaysTo subtract one date from another in Excel, subtract the earlier date cell from the later one: if…
- VLOOKUP Excel: Extended Uses, Examples, and LimitsVLOOKUP can do more than retrieve one value from a simple list: it can use exact or approximate…
- Gross Margin Excel Calculation: Formula and ExamplesWith revenue in B2 and cost of goods sold (COGS) in C2, calculate gross margin in Excel with…
- COUNTIF in Excel: Count Cells That Contain TextTo count cells in Excel that contain a particular text anywhere in the cell, use COUNTIF with an…
- Excel Sum Every Other Column: Formulas and ExamplesTo sum every other column in an Excel row, use =SUMPRODUCT((MOD(COLUMN(B2:K2)-COLUMN(B2),2)=0)*B2:K2). With numbers in B2:K2, this adds B2,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.