Function Problems

Combine Employee IDs and Email Addresses in One Excel Column

Bushra ParveenBushra Parveen Nov 12, 2026 868 views

Question details

Create one result column that shows an employee ID when available and otherwise shows the employee's email address.

Combine Employee IDs and Email Addresses in One Excel Column
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the combined-column formula

Select cell D2 and enter =IF(B2="",C2,B2), then press Enter.

2
Fill the formula down the result column

Select D2 and drag the fill handle down, or double-click the fill handle, to copy the formula to the remaining rows.

3
Apply the nine-digit ID format

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.

Use an IF formula and a nine-digit custom number format
Result: Rows with an employee ID show a nine-digit ID, such as 000123456. Rows where column B is blank show the email address from column C.
WPS Spreadsheets

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. 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. 2. Add the IF formula: Click the first result cell, such as D2, enter =IF(B2="",C2,B2), and press Enter.
  3. 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. 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.
Use IF formulas to return an ID or email based on whether an ID cell is blank.Apply the 000000000 custom format to display nine-digit employee IDs.Open, edit, and save Microsoft Excel XLSX and XLS files with strong format compatibility.Use a familiar spreadsheet interface for a smooth transition from Excel.
microsoft office alternative - wps office

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.