To concatenate values and keep dates, currency, percentages, or leading zeros readable, convert those values to formatted text with Excel’s TEXT function before joining them. For example, if A2 contains a label, B2 a date, and C2 an amount, combine them with =A2&" "&TEXT(B2,"mmm d, yyyy")&" — "&TEXT(C2,"$#,##0.00"). Concatenation creates text; it does not carry over the original cells’ number formats, so formatting must be specified in the formula.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows. The formulas below also work in current Excel for Mac and Excel for the web.
- Cells containing the values you want to combine, plus a blank cell for the result.
- The display formats you want to show, such as a date pattern, currency symbol, decimal places, or a fixed number of digits.
Step-by-Step
- Identify the values and the format each one needs. Decide what should appear in the finished text, including spaces, punctuation, and labels. For instance, a report line might contain a customer name, a date, and a total. Check the source cells too: a date should be a real Excel date if you want to format it reliably, and an amount should be a number rather than text. Note that Excel may display a number differently from its underlying value. Concatenation uses the underlying value unless you format it as text in the formula.
- Choose a blank result cell. Click the cell where you want the combined text to appear, such as D2. Do not type over a source cell unless you have already copied or no longer need its original contents. If you are combining a column of records, plan to put the first result beside the first source row so that the formula can be filled down later.
- Join ordinary text with the ampersand operator. If A2 contains a first name and B2 a last name, you can join them and include a space between them. With the first name in A2 and last name in B2, enter
=A2&" "&B2in the result cell. If A2 is Maya and B2 is Chen, the result is Maya Chen. The ampersand is a simple way to combine cell references and typed text; the quoted space is a separator, not a formatting setting. - Wrap values that need a particular display format in TEXT. Suppose A2 contains the label Invoice due, B2 contains the real date November 3, 2026, and C2 contains the number 1250.5. In D2, enter
=A2&" "&TEXT(B2,"mmm d, yyyy")&" — "&TEXT(C2,"$#,##0.00"). The result is Invoice due Nov 3, 2026 — $1,250.50. The first TEXT pattern displays the date with an abbreviated month, day, and four-digit year; the second adds a dollar sign, thousands separator, and two decimal places. Replace the patterns with the format you want to show. - Use the correct pattern for each type of value. In TEXT, the first argument is the value to convert and the second, in quotation marks, is a format code. For a date such as November 3, 2026,
TEXT(B2,"mm/dd/yyyy")displays 11/03/2026. For a value of 0.15,TEXT(C2,"0%")displays 15%. For a numeric ID that must show five digits, useTEXT(A2,"00000"); a value of 42 then displays as 00042. Put the TEXT result inside the larger concatenation formula where that value should appear. - Choose a separator that makes the result readable. Type separators in quotation marks between references. For example, if A2 contains a city and B2 a state abbreviation, the formula
=A2&", "&B2displays Seattle, WA. You can use a space, slash, colon, dash, or a phrase such as" is due on ". Include separators only where you want them; Excel does not insert them automatically. - Confirm the result and fill the formula down if needed. Press Enter and check the output against the source values, especially dates, decimal places, and zeros at the start of an ID. To apply the formula to more rows, select the result cell and drag its fill handle (the small square at the lower-right corner) down the column. Excel adjusts relative references for each row. Alternatively, copy the cell, select the destination range, and paste. For a large table, convert the source range to a table with Insert > Table; formulas in a table column can fill through the rows automatically.
- Keep the completed text or keep the formula, as appropriate. A formula result updates when its source cells change. If you need a fixed snapshot, copy the result cells and use Home > Paste > Paste Values. This replaces formulas with their current text results. It does not restore the source cells’ separate formatting, because the combined result is one text value.
You can also combine a range using TEXTJOIN when you want one consistent separator and want to ignore blank cells. With names in A2:A5 and a comma followed by a space between names, enter =TEXTJOIN(", ",TRUE,A2:A5). If A2 is Ana, A3 is blank, A4 is Bo, and A5 is Cam, the result is Ana, Bo, Cam. TEXTJOIN joins the cell contents as displayed text in many ordinary cases, but use TEXT around a date, percentage, or other number when you need a specific format. CONCAT can join ranges or individual values, but unlike TEXTJOIN it does not add a delimiter for you.
Common Mistakes
- Expecting source formatting to transfer. A date displayed as Nov 3, 2026 may appear as a serial number when joined without TEXT. Likewise, a percentage may appear as 0.15 instead of 15%. Apply the desired format inside TEXT before combining the value.
- Forgetting quotation marks around literal text or format codes. A separator such as a space must be written as
" ", and a TEXT pattern must be in quotation marks. If a formula includes words, punctuation, or a symbol you want displayed exactly, put that literal content in quotes. - Leaving out separators. Joining A2 and B2 directly produces MayaChen, not Maya Chen. Add the space or punctuation explicitly, and check the ends of each piece so you do not create double spaces.
- Assuming a concatenated result remains a number or date. Once values are combined, the result is text. You cannot reliably total a text string containing a currency label as if it were the original amount. Keep the numeric source columns for calculations and create a separate text column for presentation.
- Using the wrong format pattern. In an Excel format code,
mmcan mean month in a date pattern, whilemcan mean minutes when used in a time context. Test the output and use a clear date pattern such as"mmm d, yyyy"when you want a readable date.
Tips
- Use the ampersand for a short, customized result, TEXTJOIN for a range with a repeated separator, and CONCAT when you simply want to join values without a delimiter. These functions are available in Microsoft 365; the ampersand operator is also supported in older Excel versions.
- Use a format pattern that matches what the reader needs.
"$#,##0.00"is suitable for a dollar-style display, while"0.0%"shows one decimal place in a percentage. If the workbook uses a different currency or regional convention, change the symbol and pattern accordingly. - For telephone numbers, product codes, and account identifiers, consider storing the value as text from the beginning if leading zeros are meaningful. If it is stored numerically, a pattern such as
"00000"can add zeros in the concatenated display, but it does not change the underlying number. - Keep formulas easier to maintain by using cell references for changing content and quoted text only for fixed labels and separators. If a phrase appears in many formulas, put it in a cell and reference that cell rather than retyping it in every formula.
- If a date or currency needs to follow the workbook’s locale, check the output on the computer or service where it will be used. Date and number conventions can differ by regional settings, so a fixed pattern may not look the same to every audience.
Troubleshooting
The result shows a number instead of a date. Confirm the source cell contains an actual date and wrap its reference in TEXT with the date pattern you want, such as "mmm d, yyyy". If the source is text that only looks like a date, Excel may not interpret it as a date value; correct the source data or convert it before applying a date format.
The formula appears in the cell instead of its result. Check whether the cell is formatted as Text. Change it to General using Home > Number Format > General, then re-enter the formula. Also confirm that the formula starts with an equals sign and that Show Formulas is not turned on; press Ctrl+` on Windows to toggle that view. On Mac, use Control+` where that key is available, or toggle Show Formulas from the Formulas tab.
You see #VALUE! or an unexpected result. Check that every cell reference is valid and that the TEXT pattern is inside balanced quotation marks and parentheses. If a date value is invalid, TEXT cannot format it as a real date. Test the individual pieces in separate cells first, then rebuild the complete formula. When entering a formula by hand, use the comma argument separators shown here; Excel may use different separators under some regional settings, in which case follow the separator Excel accepts in your installation.
Leading zeros disappear. If the source identifier is numeric, explicitly apply a zero pattern with TEXT, such as "00000". If the identifier has letters or has a variable structure, store it as text and preserve its exact characters rather than treating it as a number.
Filling down repeats the same result. Inspect the references in the next row. Normal references such as A2 and B2 should shift to A3 and B3 when filled down. If you used dollar signs to lock a reference, such as $A$2, Excel will intentionally keep pointing to that cell; remove the dollar signs from references that should change by row.
Frequently Asked Questions
Can Excel concatenate cells and preserve their font, color, or bold formatting?
Not with a normal formula. A formula returns one value, and a concatenated result is text; it does not retain separate rich-text formatting from each source cell. You can format the entire result cell, or use TEXT to control number displays such as dates and currency. Applying different fonts or colors to separate parts requires manual rich-text formatting or another approach, not ordinary concatenation.
What is the difference between CONCAT, CONCATENATE, and the ampersand?
All can join text. The ampersand is a compact operator, CONCAT is a function that can accept ranges, and CONCATENATE is the older function name retained for compatibility. None automatically inserts spaces or punctuation, and none preserves source-cell number formats by itself.
Does TEXT change the original cell?
No. TEXT converts a value to formatted text within the formula result. The original date or number remains unchanged in its source cell and can still be used for calculations.
Can I concatenate a date and amount in Excel for the web or Mac?
Yes. The formulas in this guide work in current Excel for the web and Excel for Mac as well as Excel for Microsoft 365 on Windows. Ribbon placement and keyboard shortcuts can differ, but entering the formula and using TEXT work the same way.
Why does my formula use semicolons instead of commas?
Excel’s list separator can depend on regional settings. The examples use commas as requested for standard formula syntax. If your Excel installation expects semicolons between function arguments, replace those argument commas with semicolons; do not change commas that are inside quoted text or format codes.
Related
- Capitalize the First Word in Excel
- Capitalize All Letters in Google Sheets with UPPER
- Capitalize All Names in Excel with PROPER and Fill Down
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- FLOOR Excel Formula vs. ROUNDDOWN: Key DifferencesIn Excel, FLOOR rounds a number down to a chosen multiple, while ROUNDDOWN cuts off digits at a…
- Sort Excel by Color: Cells, Font Colors, and IconsTo sort Excel rows by color in Microsoft 365 for Windows, select your data, choose Data > Sort,…
- VLOOKUP Showing Formula? Causes and Fixes in ExcelIf VLOOKUP is showing as text instead of returning a result, first turn off Formulas > Show Formulas;…
- How to Filter Dates in Excel by Month, Year, or RangeTo filter dates in Excel, select a cell in your data, choose Data > Filter, open the arrow…
- Excel Conditional Formatting Based on a FormulaYes. In Excel for Microsoft 365 on Windows, select the cells to format, then choose Home > Conditional…
- XLOOKUP to Compare Two Sheets in Excel Step by StepIf Sheet1 has IDs in A2:A100 and Sheet2 has IDs in A2:A100, enter this in Sheet1 cell B2…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.