Combine Employee IDs and Email Addresses in One Excel Column
Question details
Create one result column that shows an employee ID when available and otherwise shows the employee's email address.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Employee IDs are stored in one column and email addresses are stored in another column. The user needs a combined result column that prioritizes the ID.
- Observed behavior
- Blank employee ID cells should return the corresponding email address, while numeric IDs should display as nine digits with leading zeros.
Confirm that the employee ID is in column B and the email address is in column C, starting on row 2. Make sure the ID cells contain numeric values if you want the nine-digit custom format to add leading zeros.
Use an IF formula and a nine-digit custom number format
Use this method when each row should show the employee ID from column B, or the email address from column C when the ID is blank.
The IF formula returns text for email addresses and numeric values for employee IDs. A custom number format displays numeric IDs with leading zeros without changing email addresses.
Select cell D2 and enter =IF(B2="",C2,B2), then press Enter.
Select D2 and drag the fill handle down, or double-click the fill handle, to copy the formula to the remaining rows.
Select column D, open Format Cells, choose Custom, and enter 000000000 in the Type box. Click OK to display numeric employee IDs as nine digits.

Combine Employee IDs and Emails Easily in WPS Spreadsheets
WPS Spreadsheets can use the same IF formula to combine an employee ID and email address into one result column. Apply a custom nine-digit format to preserve leading zeros for numeric IDs while keeping email text readable.
- 1. Open the worksheet in WPS Spreadsheets: Open your Excel workbook in WPS Office and select the worksheet containing the employee ID and email columns.
- 2. Add the IF formula: Click the first result cell, such as D2, enter =IF(B2="",C2,B2), and press Enter.
- 3. Copy the formula to other rows: Select the formula cell and drag its fill handle down the column to populate the remaining records.
- 4. Set the custom ID display format: Select the result column, right-click, choose Format Cells, select Custom, and enter 000000000 as the format code.

Frequently Asked Questions
Why does the formula return the email address instead of the employee ID?
The formula =IF(B2="",C2,B2) returns the value in C2 only when B2 is blank. Check whether the employee ID cell in column B is empty or contains a formula that returns an empty string.
Will the custom format 000000000 change email addresses?
No. The custom format applies to numeric values, so numeric employee IDs display with leading zeros while text values such as email addresses remain unchanged.
How do I keep an employee ID that starts with zero?
Apply the custom number format 000000000 to the result column. For example, the numeric value 123456 displays as 000123456.
Can I combine columns when the employee ID is stored as text?
Yes. The IF formula still works. If the ID is already text with its leading zeros included, it will display as stored and does not need the custom numeric format.




