Combine Business and Mobile Phone Numbers with an Excel IF Formula
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.
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.
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.
Select an empty output cell, such as N2, enter =IF(L2="",IF(M2="","",M2),IF(M2="",L2,L2&"•"&M2)), and press Enter.
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.
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.
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. Open the contact workbook: Launch WPS Spreadsheet and use Open to select your Excel workbook.
- 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. 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. 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.

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.




