Joining texts

%3CLINGO-SUB%20id%3D%22lingo-sub-1571621%22%20slang%3D%22en-US%22%3EJoining%20texts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571621%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%20just%20a%20quick%20question.%20Been%20looking%20at%20the%20discussions%20on%20here%20to%20expand%20my%20knowledge.%20I%20came%20across%20the%20question%20of%20joining%20texts%20the%20day%20and%20I%20was%20just%20wondering%20what%20the%20advantage%20of%20these%20approaches%20are%20in%20comparison%20to%20one%20another%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCONCATENATE()%3C%2FP%3E%3CP%3E%3Dcell1%20%26amp%3B%20cell2%3C%2FP%3E%3CP%3E%3Djointext()%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20see%20that%20jointext%20allows%20you%20to%20exclude%20blanks%20and%20to%20put%20a%20delimiter%20between%20the%20joined%20words%20and%20you%20can%20make%20sure%20that%20there%20is%20no%20delimiter%20after%20the%20last%20piece%20of%20joined%20text.%20So%20I%20can%20see%20a%20clear%20advantage%20of%20using%20this%20formula.%20You%20can%20also%20use%20an%20array%20rather%20than%20having%20to%20add%20individual%20cell%20references.%20So%20jointext()%20is%20great%20and%20I%20will%20use%20it%20in%20the%20future.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20what%20is%20the%20advantage%20of%20%3CSPAN%3ECONCATENATE()%3C%2FSPAN%3E%20over%20simply%20referencing%20the%20cells%20and%20joining%20with%20%26amp%3B%3F%3F%3F%3F%20There%20might%20not%20be%20one%2C%20however%2C%20I%20thought%20I%20ask%20whether%20i%20am%20missing%20something%20here...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1571621%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-1571642%22%20slang%3D%22en-US%22%3ERe%3A%20Joining%20texts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571642%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F740158%22%20target%3D%22_blank%22%3E%40Poogermum%3C%2FA%3E%26nbsp%3BJust%20for%20reference%2C%20the%20function%20in%20English%20Excel%20is%20called%20TextJoin()%2C%20not%20jointext.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EConcatenate()%20offers%20no%20advantages%20over%20using%20the%20ampersand%20symbol%20%22%26amp%3B%22.%20It%20only%20exists%20to%20provide%20compatibility%20with%20other%20spreadsheet%20software.%20I%20think%20it%20goes%20back%20to%20the%20days%20of%20Lotus%201-2-3.%20In%20order%20to%20open%20spreadsheets%20that%20were%20created%20with%20Lotus%201-2-3%20and%20were%20using%20the%20Concatenate()%20function%2C%20Excel%20had%20to%20have%20that%20function%20too%2C%20so%20the%20spreadsheet%20would%20not%20break.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EToday%2C%20there%20is%20no%20reason%20whatsoever%20to%20use%20Concatenate().%20Using%20the%20%26amp%3B%20sign%20is%20always%20shorter.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi, 

Hopefully just a quick question. Been looking at the discussions on here to expand my knowledge. I came across the question of joining texts the day and I was just wondering what the advantage of these approaches are in comparison to one another:

 

=CONCATENATE()

=cell1 & cell2

=jointext()

 

I can see that jointext allows you to exclude blanks and to put a delimiter between the joined words and you can make sure that there is no delimiter after the last piece of joined text. So I can see a clear advantage of using this formula. You can also use an array rather than having to add individual cell references. So jointext() is great and I will use it in the future.

 

However, what is the advantage of CONCATENATE() over simply referencing the cells and joining with &???? There might not be one, however, I thought I ask whether i am missing something here...

2 Replies

@Poogermum Just for reference, the function in English Excel is called TextJoin(), not jointext.

 

Concatenate() offers no advantages over using the ampersand symbol "&". It only exists to provide compatibility with other spreadsheet software. I think it goes back to the days of Lotus 1-2-3. In order to open spreadsheets that were created with Lotus 1-2-3 and were using the Concatenate() function, Excel had to have that function too, so the spreadsheet would not break.

 

Today, there is no reason whatsoever to use Concatenate(). Using the & sign is always shorter.

thanks great reply!