Function Problems

Combine Business and Mobile Phone Numbers with an Excel IF Formula

Rana GarciaRana Garcia Nov 13, 2026 869 views

Question details

Create an Excel formula that displays the business phone number when the mobile number is blank and joins both numbers with a bullet when both are available.

Product
Microsoft Excel
Device & OS
not provided
Scenario
Business phone numbers are in column L and mobile phone numbers are in column M, starting at row 2. A separate column should display the available contact numbers.
Observed behavior
An earlier formula produced an unexpected character when both phone fields were empty. The desired result is a blank cell for empty rows, one number when only one is available, and two numbers separated by a bullet when both exist.
Before you start

Confirm that L2 contains the business number and M2 contains the mobile number, or replace those references with your actual cells. Store phone numbers as text before entering or importing them so leading zeros and plus signs are preserved.

Solution 1Recommended

Use a nested IF formula to combine only available phone numbers

Use this formula to handle both populated cells, either missing number, and rows where both phone fields are blank.

The formula checks each phone field before joining the values. It avoids a stray bullet or a zero when neither number is present and also handles cells whose formulas return an empty string.

1
Enter the formula

Select an empty output cell, such as N2, enter =IF(L2="",IF(M2="","",M2),IF(M2="",L2,L2&"•"&M2)), and press Enter.

2
Check the four possible results

Review rows with both numbers, only a business number, only a mobile number, and neither number. The formula returns business•mobile, the business number, the mobile number, and an empty string, respectively.

3
Apply the formula to the remaining rows

Select N2 and drag the fill handle at the lower-right corner of the cell down through the contact list. Excel adjusts L2 and M2 to the corresponding row references.

Spaces are not blank values: A cell containing spaces is not equal to an empty string. If an apparently empty phone field still produces a bullet, select that source cell and clear its contents.
Combine contact numbers in WPS Spreadsheet

Build the same phone-number formula in WPS Spreadsheet

WPS Spreadsheet supports IF formulas and ampersand concatenation, so you can combine business and mobile numbers with the same blank-cell checks in your Excel workbook.

  1. 1. Open the contact workbook: Launch WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Locate the source phone fields: Select the business and mobile phone columns and confirm their cell references. Use L2 and M2 if they match the example.
  3. 3. Enter the combining formula: Click an empty result cell and enter =IF(L2="",IF(M2="","",M2),IF(M2="",L2,L2&"•"&M2)), then press Enter.
  4. 4. Fill down and save: Drag the result cell's fill handle down the contact rows, then choose Save As and select .xlsx if you need to share the workbook with Excel users.
Combine two phone numbers with a bullet only when both are available.Keep the result blank when both source phone fields are empty.Work with Microsoft Excel .xlsx and .xls files.Fill the formula down an entire contact list using familiar spreadsheet controls.
microsoft office alternative - wps office

Frequently Asked Questions

What happens when both phone-number cells are blank?

The formula returns an empty string, so the result appears blank instead of showing a zero or a bullet. Cells containing spaces are not treated as blank; clear those source cells if necessary.

Will the formula show the mobile number if the business number is missing?

Yes. When L2 is blank and M2 contains a number, the formula returns M2 without adding a bullet.

How do I add spaces around the bullet?

In the formula bar, replace L2&"•"&M2 with L2&" • "&M2. The blank-cell checks stay the same.

Why are leading zeros missing from the combined phone numbers?

Concatenation uses the underlying cell value, not a custom number format. Set the source phone cells to Text before entering or importing the numbers. Changing an existing numeric cell to Text does not restore zeros that have already been removed.