How to Combine Cells While Preserving Leading Zeros in Excel
Question details
The user needs to merge multiple cells containing two-digit text values into a single continuous string without losing the leading zeros.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Combining data from multiple columns (e.g., B through H) into one cell for data formatting, identification codes, or reporting.
- Observed behavior
- Standard number formatting drops leading zeros, but text concatenation preserves them as long as the source cells are stored as text.
Ensure that the source cells containing the leading zeros are formatted as Text before you begin combining them, otherwise the spreadsheet may drop the zeros during the calculation.
Use the Ampersand (&) Operator to Combine Cells
The simplest and most universally compatible method to concatenate cells across all versions of Excel.
The ampersand (&) operator links the contents of multiple cells together into a single text string. Because it treats the cell contents as text, any leading zeros already present in text-formatted source cells will be perfectly preserved.
Click on the empty cell where you want the combined result to appear.
Type the formula =B1&C1&D1&E1&F1&G1&H1 into the formula bar, adjusting the cell references to match your actual data range.
Press Enter to apply the formula and view the combined text string.
Click and drag the small square fill handle at the bottom-right corner of the cell down to apply the formula to the remaining rows.
Use the CONCAT or TEXTJOIN Function (Newer Excel Versions)
A cleaner approach for newer versions of Excel (2019 and Microsoft 365) that avoids typing every single cell reference manually.
Use the CONCATENATE Function (Older Excel Versions)
A legacy function available in older versions like Excel 2013 and earlier for merging cell contents.
Combine Cells and Manage Data Easily with WPS Spreadsheet
WPS Office provides a highly compatible and user-friendly spreadsheet application that supports all standard Excel functions, including CONCAT, TEXTJOIN, and the ampersand operator, ensuring your data formatting remains perfectly intact.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the cells you want to combine.
- 2. Select the destination cell: Click on the empty cell where you want the combined data to appear.
- 3. Enter the formula: Type your preferred formula, such as =CONCAT(B1:H1) or =B1&C1&D1, into the formula bar.
- 4. Apply and fill down: Press Enter to combine the cells, then use the fill handle to drag the formula down the column for the remaining data.
Frequently Asked Questions
Why do my leading zeros disappear when I combine cells?
Leading zeros disappear if the spreadsheet interprets the combined result or the source cells as numbers rather than text. Ensure your source cells are formatted as Text before combining them.
Can I add a delimiter like a comma or space when combining cells?
Yes. You can use the TEXTJOIN function, like =TEXTJOIN("-", TRUE, B1:H1), or manually add delimiters using the ampersand operator, such as =B1&"-"&C1.
How do I force a leading zero if the source cell is formatted as a number?
You can use the TEXT function within your concatenation formula to force a specific format. For example, =TEXT(B1,"00")&TEXT(C1,"00") will ensure each value is treated as a two-digit text string with leading zeros, regardless of the original cell format.
