Forum Discussion

leftnotracks's avatar
leftnotracks
Copper Contributor
Jul 28, 2019

Combine contents of two text cells with a condition

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 ...
excel
Formulas and Functions
nabilmourad's avatar
nabilmourad
MVP
Jul 28, 2019

leftnotracks 

 

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

Resources