To concatenate values in Excel and keep leading zeros, format the value with TEXT inside the formula before joining it to anything else. For example, with a number in A2 that should appear as three digits and another number in B2 that should appear as four digits, use =TEXT(A2,"000")&"-"&TEXT(B2,"0000"); if A2 is 7 and B2 is 42, the result is 007-0042.
Why zeros disappear when you concatenate
Excel treats numbers and text differently. A numeric value of 42 is stored as the number 42, not as a four-character string such as 0042. When a formula joins that number to other content, Excel normally uses its underlying value, rather than the way the cell happens to look on screen. As a result, concatenating a displayed 0042 can produce 42.
This is expected behavior, not a fault in the concatenation operator. Leading zeros generally do not change a number’s mathematical value, so Excel removes them when interpreting an entry as numeric. But zeros can be meaningful in identifiers: product codes, account references, postal codes, employee numbers, and fixed-length labels are often strings of digits rather than quantities to calculate with.
The key question is therefore whether the value is genuinely numeric or is an identifier that should be treated as text. For calculations, keep a value numeric and apply a number format when you only need to change its display. For identifiers, preserve or create the exact character string you need before joining values.
Use TEXT to add zeros within the formula
The TEXT function converts a value to text using a format pattern. In a pattern made of zero placeholders, each zero marks a digit position that should display even when the corresponding digit is missing. This makes it useful for fixed-width codes and numbers that must have a consistent number of characters.
With the number 42 in A2 and a four-digit output requirement, use =TEXT(A2,"0000"). The result is the text 0042. The source cell remains numeric; the formula creates a text version for the result. A format with three zeros creates at least three displayed digits, while a format with five zeros creates at least five. If the value has more digits than the pattern, Excel does not truncate it.
For a combined identifier, with A2 containing 7 and B2 containing 42, use =TEXT(A2,"000")&"-"&TEXT(B2,"0000"). The result is 007-0042. The hyphen is a literal separator, and the ampersands join the formatted text pieces. Adjust the number of zeros to match the code specification; do not choose a width merely because it looks tidy.
Formatting codes for numbers and dates are not identical to the visible characters in a cell. In particular, TEXT returns text, so the result is not suitable for arithmetic without converting it back to a number. That trade-off is appropriate for labels and codes, but not usually for values that need to be summed or averaged.
Choose the right way to concatenate
Excel offers several ways to join text. The ampersand operator is direct and easy to read for a few pieces. The CONCAT function joins values or cell references, while TEXTJOIN can join a range with a chosen delimiter and can skip empty cells. None of these methods automatically preserves a numeric cell’s custom display format. Format the number as text first, regardless of which joining method you choose.
- Use
&for short formulas with a small number of parts, such as a padded code, a hyphen, and a second code. - Use
CONCATwhen you prefer a function-style formula for joining several values. - Use
TEXTJOINwhen a delimiter should be inserted between multiple cells, especially if some may be blank.
For example, with a numeric value in A2 that must be four digits and text in B2, use =TEXT(A2,"0000")&B2. If A2 is 42 and B2 contains the text X, the result is 0042X. If the second part is also a number that needs its own width, give it its own TEXT pattern rather than expecting the first pattern to affect it.
For dates, explicitly choose the desired display form as well. With a date in A2 and a text label in B2, use =TEXT(A2,"yyyy-mm-dd")&" "&B2. If A2 contains October 5, 2026, and B2 contains Report, the result is 2026-10-05 Report. Without TEXT, a joined date may appear as its underlying date serial number instead of a familiar date.
Make a fixed-width code from a number
To build one padded code from a numeric source, decide the required width first. Suppose product numbers are defined as six digits, so the number 315 must appear as 000315. With that number in A2, use =TEXT(A2,"000000"); the result is the text 000315. You can then append a category or separator in the same formula, or use the padded result as an intermediate column.
A fixed width is a rule, not a guess. If codes may have different lengths, or if leading zeros are not always meaningful, a single zero pattern may not describe the data correctly. Check the source system’s specification, a reliable reference list, or other records before padding. Adding zeros to a value that was already shortened can make it look plausible without restoring the original identifier with certainty.
Also consider whether codes can contain letters, spaces, or punctuation. A number-only formula is not a substitute for preserving a mixed-character code as text. If a value can be 00A7, for instance, it should be stored as text because Excel cannot treat the whole code as a number.
Use cell formatting only when display is enough
A custom number format can show leading zeros in a cell without changing the underlying numeric value. Select the cells, open Format Cells with Ctrl+1 on Windows, choose the Number tab, select Custom, and enter a pattern such as 0000 in the Type box. On Mac, Command+1 opens Format Cells; the available dialog layout can differ slightly. In Excel for the web, use the Number Format controls on the Home tab; the desktop dialog may offer more format options.
This method is helpful when people need to see a fixed-width number in the worksheet but still need to calculate with it. For example, formatting numeric 42 with 0000 displays 0042 while the stored value remains 42. However, concatenation usually uses the stored value, not the custom display. A cell that looks like 0042 can therefore still yield 42 when joined. Use TEXT in the formula when the joined result itself must contain the zeros.
Formatting existing numbers as Text is different again. Changing a cell’s number format to Text does not reliably turn a number already stored there into its original zero-padded string. It also cannot recover zeros that were discarded when the data was first entered or imported.
Preserve zeros before they are lost
If you control data entry, store identifiers as text from the start. You can format destination cells as Text before entering or pasting codes, or enter a leading apostrophe before a code such as 0042. Excel uses the apostrophe as an instruction to treat the entry as text; it does not appear as part of the cell’s displayed value. These approaches are useful for codes, but they are usually not right for quantities that need arithmetic.
When importing a file, inspect how Excel interprets identifier columns before relying on them. A CSV file does not preserve Excel’s cell formatting, and opening a file directly may cause digit-only fields to be interpreted as numbers. In Microsoft 365 for Windows, importing through Data > From Text/CSV gives you a chance to review the data before loading it. Where the import options allow a column’s type to be specified, set identifier columns to text. The precise controls can vary with the data source and Excel build.
If the source is already numeric and its zeros have disappeared, recovery requires outside information. You may be able to restore a code if the required width is known, but Excel cannot determine whether 42 was originally 42, 042, or 0042 on its own. Confirm the width from a trusted source rather than assuming that the most common length is correct.
Common problems and how to fix them
- The result still has no zeros: Check that the number is wrapped in
TEXTinside the concatenation formula. A custom format on the source cell alone is not enough. - The result contains unexpected digits: Confirm that the format pattern has the required number of zero placeholders and that the source value is the one you intended to use.
- A date becomes a large number: Format the date within the formula using a date pattern, such as
yyyy-mm-dd, before joining it to text. - The formula shows as text instead of calculating: Check whether the formula cell is formatted as Text. Change it to General, then re-enter the formula. Also check for an accidental leading apostrophe.
- The formula displays an error: In Excel’s standard English-language syntax, use straight quotation marks around format patterns and text literals, balanced parentheses, and commas between function arguments. Regional settings can affect the argument separator.
- A code has already lost its zeros: Establish the intended width from a reliable specification or source file. Changing number format cannot recover information that was not retained.
One common misconception is that the ampersand is the problem. It is not: & joins the values it receives. The missing zeros usually result from joining a numeric value without first converting it to text in the required format. Another misconception is that every digit-only field should be numeric. If a field identifies something rather than measuring it, preserving its text representation is often the safer choice.
Check the result before sharing it
After building a concatenation formula, verify both the appearance and the meaning of the output. Compare a few results against the original records, including the shortest code, the longest code, a blank or missing part, and any value with a leading zero. If the formula will be filled down, confirm that the references move as intended and that the format width remains appropriate for every row.
Remember that a formula returning padded text is not the same as a numeric result with a display format. That distinction matters when exporting a worksheet, matching codes against another file, sorting, or using the result in another formula. Text identifiers are generally best compared with other text identifiers. If the output will be sent to a system that expects a particular width or delimiter, check its requirements before distributing the workbook.
Frequently Asked Questions
Can I concatenate cells and keep their existing number formats?
No. A concatenation formula does not automatically carry over custom number formats from referenced cells. Use TEXT in the formula to specify how numeric values should appear in the combined text.
Does formatting a cell as Text restore leading zeros?
No. Text formatting can help preserve future entries, but it does not recover zeros already removed from a stored number. You need the original source or a known code width to reconstruct them.
Will TEXT make my result a number or text?TEXT returns text. That is usually what you want for a joined identifier with leading zeros, but it means the result should not be treated as a numeric quantity without an intentional conversion.
Can I use a custom format instead of TEXT?
Yes, if you only need the source cell to look padded and want to keep its numeric value for calculations. If the zeros must appear in the concatenated result, use TEXT in the formula as well.
What if a code has letters as well as leading zeros?
Store the complete code as text, such as 00A7. A numeric format only formats numbers and cannot preserve a mixed-character identifier as a numeric value.
Can I concatenate several cells with a delimiter?
Yes. Use TEXTJOIN when you need a repeated delimiter between multiple cells, and apply TEXT to any numeric parts that need padding. Check for blank cells if their positions or separators matter to the final code.
Related
- Can You Insert an Image Into an Excel Cell?
- Can You Delete Duplicates in Excel? Methods and Tips
- Delete Multiple Tabs in Excel at Once: How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Share a Calendar in Outlook with the Right AccessTo share a calendar in Outlook, open Calendar, choose the calendar you own, select Share or Share Calendar,…
- Excel COUNT Formula Examples: Numbers, Blanks, and TextIn Excel for Microsoft 365, use =COUNT(A2:A20) to count cells containing numbers, including dates and times. For other…
- COUNTIF in Excel Between Two Values: The Right FormulaTo count numbers between two limits in Excel, use COUNTIFS, which applies both limits to the same range.…
- Dynamic Drop-Down Lists in Excel Using INDIRECT: How-ToYes. To make a dependent drop-down in Excel, create a named range for each list, then set the…
- Round Excel to Significant Figures: Formulas and MethodsTo round a number in Excel to a chosen number of significant figures, put the value in A2…
- Excel Formula to Find All Tab Names in Your WorkbookExcel has no built-in worksheet formula that directly returns every tab name. In Microsoft 365 for Windows, you…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.