Combine contents of two text cells with a condition

Occasional Visitor

I'm preparing a spreadsheet for Data Merge in Indesign. InDesign does not support conditions. The data contains phone numbers and extensions, but not all records have an extension. For phone numbers with an extension I want the phone number to import as "000-000-0000 ext.000". For phone numbers without an extension I want the phone number to import as "000-000-0000". I want to create a new column that merges the phone number and extension into one cell so the it imports correctly whether there is an extension or not.


Does this make sense? How do I enter the formula to combine the fields with a condition for adding " ext."?


Edit: I think I got it.

=F2&IF(G2>0," ext."&G2,)

2 Replies
Yep that looks pretty much right, I'd change the >0 to <> ""

=F2 & IF( G2 <> "", " ext."& G2, "" )



Greetings to you and to my friend (The Excel Guru)  @Wyn Hopkins   
Another option without formulas:

You may use Flash Fill:

Assuming you have Phone numbers in Column A and You have some extensions in Column B

  • In Column C Type the combined phone number (Phone & extension)
  • Type another with No extension
  • You just created a Pattern for the Flash Fill to follow
  • Select the whole destination Range in column C 
  • Hit CTRL +E

You get an entire column



Note that I have my phone numbers in the above screenshot "Left Aligned" >> because I typed everything including the brackets (which is not the professional way of doing things)

However, even if you just type the numbers and let excel do the formatting for the phone (CTRL + 1 >> Special >> Phone >> OK) & for the extension (CTRL +1 >> Number Tab >> Custom >> Type : "ext "###  >> OK

If you use the same steps mentioned above, the Flash Fill will be able to generate an entire column with extensions (if they exist)



Hope that helps

Nabil Mourad

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
17 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies