Home

Combine contents of two text cells with a condition

%3CLINGO-SUB%20id%3D%22lingo-sub-776348%22%20slang%3D%22en-US%22%3ECombine%20contents%20of%20two%20text%20cells%20with%20a%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776348%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20preparing%20a%20spreadsheet%20for%20Data%20Merge%20in%20Indesign.%20InDesign%20does%20not%20support%20conditions.%20The%20data%20contains%20phone%20numbers%20and%20extensions%2C%20but%20not%20all%20records%20have%20an%20extension.%20For%20phone%20numbers%20with%20an%20extension%20I%20want%20the%20phone%20number%20to%20import%20as%20%22000-000-0000%20ext.000%22.%20For%20phone%20numbers%20without%20an%20extension%20I%20want%20the%20phone%20number%20to%20import%20as%20%22000-000-0000%22.%20I%20want%20to%20create%20a%20new%20column%20that%20merges%20the%20phone%20number%20and%20extension%20into%20one%20cell%20so%20the%20it%20imports%20correctly%20whether%20there%20is%20an%20extension%20or%20not.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20this%20make%20sense%3F%20How%20do%20I%20enter%20the%20formula%20to%20combine%20the%20fields%20with%20a%20condition%20for%20adding%20%22%20ext.%22%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEdit%3A%20I%20think%20I%20got%20it.%3C%2FP%3E%3CP%3E%3DF2%26amp%3BIF(G2%26gt%3B0%2C%22%20ext.%22%26amp%3BG2%2C)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-776348%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776474%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20contents%20of%20two%20text%20cells%20with%20a%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776474%22%20slang%3D%22en-US%22%3EYep%20that%20looks%20pretty%20much%20right%2C%20I'd%20change%20the%20%26gt%3B0%20to%20%26lt%3B%26gt%3B%20%22%22%3CBR%20%2F%3E%3CBR%20%2F%3E%3DF2%20%26amp%3B%20IF(%20G2%20%26lt%3B%26gt%3B%20%22%22%2C%20%22%20ext.%22%26amp%3B%20G2%2C%20%22%22%20)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776512%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20contents%20of%20two%20text%20cells%20with%20a%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776512%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383220%22%20target%3D%22_blank%22%3E%40leftnotracks%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreetings%20to%20you%20and%20to%20my%20friend%20(The%20Excel%20Guru)%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3CBR%20%2F%3EAnother%20option%20without%20formulas%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3EYou%20may%20use%20Flash%20Fill%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EAssuming%20you%20have%20Phone%20numbers%20is%20Column%20A%20and%20You%20have%20some%20extentions%20in%20Column%20B%3C%2FP%3E%3CUL%3E%3CLI%3EIn%20Column%20C%20Type%20the%20combined%20phone%20number%20(Phone%20%26amp%3B%20extension)%3C%2FLI%3E%3CLI%3EType%20another%20with%20No%20extension%3C%2FLI%3E%3CLI%3EYou%20just%20created%20a%20Pattern%20for%20the%20Flash%20Fill%20to%20follow%3C%2FLI%3E%3CLI%3ESelect%20%3CSTRONG%3Ethe%20whole%20destination%20Range%3C%2FSTRONG%3Ein%20column%20C%26nbsp%3B%3C%2FLI%3E%3CLI%3EHit%20%3CSTRONG%3ECTRL%20%2BE%3C%2FSTRONG%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSTRONG%3EYou%20get%20an%20entire%20column%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124748i37C2FCC7241F4D0D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22PhoneExt.png%22%20title%3D%22PhoneExt.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ENote%3C%2FSTRONG%3Ethat%20I%20have%20my%20phone%20numbers%20in%20the%20above%20screenshot%20%22%3CSTRONG%3ELeft%20Aligned%3C%2FSTRONG%3E%22%20%26gt%3B%26gt%3B%20because%20I%20typed%20everything%20including%20the%20brackets%20(which%20is%20not%20the%20professional%20way%20of%20doing%20things)%3C%2FP%3E%3CP%3EHowever%2C%20even%20if%20you%20just%20type%20the%20numbers%20and%20let%20excel%20do%20the%20formatting%20for%20the%20phone%20(CTRL%20%2B%201%20%26gt%3B%26gt%3B%20Special%20%26gt%3B%26gt%3B%20Phone%20%26gt%3B%26gt%3B%20OK)%20%26amp%3B%20for%20the%20extension%20(CTRL%20%2B1%20%26gt%3B%26gt%3B%20Number%20Tab%20%26gt%3B%26gt%3B%20Custom%20%26gt%3B%26gt%3B%20Type%20%3A%20%22ext%20%22%23%23%23%26nbsp%3B%20%26gt%3B%26gt%3B%20OK%3C%2FP%3E%3CP%3EIf%20you%20use%20the%20same%20steps%20mentioned%20above%2C%20the%20Flash%20Fill%20will%20be%20able%20to%20generate%20an%20entire%20column%20with%20extensions%20(if%20they%20exist)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20735px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124747iBCB9B08E2B757FE9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Proper.png%22%20title%3D%22Proper.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
leftnotracks
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, "" )

@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

PhoneExt.png

 

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)

Proper.png

 

Hope that helps

Nabil Mourad