You can use the following formula in Excel to concatenate values and keep leading zeros after the values have been concatenated:
=TEXT(A2,"0000") & B2
This particular formula concatenates the values in cells A2 and B2 and ensures that the value from cell A2 has as many leading zeros as necessary to make the length equal to four.
The following example shows how to use this formula in practice.
Example: Concatenate Values and Keep Leading Zeros in Excel
Suppose we have the following dataset in Excel that contains numeric values that represent the store number and employee ID number for various employees at some company:
Note: If Excel automatically removes leading zeros when you type them in cells, simply highlight the cell range of interest and then click the tiny arrow in the bottom right corner of the Number group on the Home tab.
In the window that appears, click Custom in the Category list and type 0000 into the Type box, then click OK.
This will ensure that the leading zeros appear in the cells.
Suppose we attempt to use the CONCAT function to concatenate the values in cells A2 and B2:
=CONCAT(A2, B2)
The following screenshot shows how to use this formula in practice:
By default, Excel will remove the leading zeros from the concatenated value.
To keep the leading zeros, we can use the following formula instead:
=TEXT(A2,"0000") & B2
We can type this formula into cell C2 and then click and drag it down to each remaining cell in column C:
Notice that the leading zeros are kept in the final concatenated value.
Note: Feel free to change the number of zeros used in the TEXT function if you have a different number of leading zeros you’d like to keep.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Remove Specific Text from Cells
Excel: How to Delete Rows with Specific Text
Excel: How to Check if Cell Contains Partial Text
Excel: How to Check if Cell Contains Text from List
