I noticed that "EXEC MySproc," & "arg, " & "arg2"
is essentially the same as =CONCATENATE("EXEC MySproc,", "arg,", "arg2")
including the ability to use evaluations, functions etecetera. What is the usecase for using =CONCATENATE()
vs &
?
It's for people who like to type more. Probably the same audience that does things like =Sum((A1*A2)A3)
instead of =(A1*A2)A3
. (Yes, these people exist, and I don't know why they do that)
It also provides wonderful opportunities to create your very first User Defined Function with VBA to create something more useful than Concatenate().
Excel 2016 (with an Office 365 subscription) has a new function called TextJoin(), which takes a delimiter and a range as arguments and is a lot faster than typing out the ampersands and the delimiters as text strings. Now, THAT is useful.

5At least SUM can take a range. Thanks for the pointer on TEXTJOIN. Work hasn't upgraded to 2016 yet, but when they do I'll finally be able to retire my UDF. – Dranon Jul 6 '17 at 4:29

4

8@xehpuk No I didn't. Some people wrap a Sum() function around simple calculations. They could use
=A1+A2
but for some reason, they write=Sum(A1+A1)
. Or=Sum(A1A2)
where they could us=A1A2
. Or=Sum(A1*A2)
where they could use=A1*A2
. Some people put all kinds of formulas into a Sum() function and I'm trying to understand why. – teylyn Jul 6 '17 at 11:44 
2@IllusiveBrian that's because you're still holding down the Shift key when you hit that spacebar. – Mathieu Guindon Jul 6 '17 at 20:47

41. While the answer is funny, it is also pointless. The question was not "Why are people using CONCATENATE instead of &?" but "Why should one does CONCATENATE instead of &?". The answer is "it is completely equivalent, the Excel online help states that & should be used instead". The correct (and helpful) explanation, e.g. from Aganju, is that & came later and CONCATENATE has been left in for compatibility reasons, which is a completely valid reason. All this "people are stupid and want to type much" is completely uncalledfor; there are reasons why they do that and it's not their stupidity. – AnoE Jul 7 '17 at 14:09
It's probably because they're using the Insert Function button.
I sometimes do this when I'm eating with my left hand and am just doing some low level formulas, or when I'm using a touch device and couldn't be bothered switching between the symbols, letters, and numbers on the touch screen.

1+1 Yup. I've always used the button, it was sufficient, so there is no incentive to learn operators. – kubanczyk Jul 6 '17 at 14:13

3To concat strings I need to remember dot
.
for perl, pipepipe
for SQL, no char for cmd and bash,+
for Java. Do I really need&
just for Excel? – kubanczyk Jul 6 '17 at 14:15 
2@kubanczyk if you can remember those
.
,
and+
then you can remember&
. It's easier thanconcatenate
, esp. if one isn't quite fluent at English. It's also used in VBA – phuclv Jul 6 '17 at 16:19 
3@LưuVĩnhPhúc Re fluency in English  as far as I know, those function names are localized (e.g., it is
VERKETTEN
in German Excel) – Hagen von Eitzen Jul 6 '17 at 18:38 
2@HagenvonEitzen another good reason to use the operator over the function! – Mathieu Guindon Jul 6 '17 at 20:49
It has only historical and compatibility reasons. Former versions of Excel didn't support one format, and other spreadsheet tools (like Google Docs, Apple Numbers, or Open Office) didn't support the other one.
Choose whichever you prefer.
Note that depending on your save format, Excel needs more space to save CONCATENATE()
than &
.

Yup. I didn't know that
&
was the replacement forCONCATENATE
until now. I rarely use excel, but when I do it's usually for something which needs concatenation – gabe3886 Jul 7 '17 at 7:52
In my opinion, the selective use of concatenate and ampersands, can lead to clearer formulas.
This concatenation of address details, using a mixture of CONCATENATE
and &
seems clearer to me:
=CONCATENATE(StreetNumber," ",StreetName," ",StreetType) & CHAR(10) & CONCATENATE(CityName," ",StateCode," ",ZipCode) & CHAR(10) & CountryName
Than the exclusive use of &
:
=StreetNumber & " " & StreetName & " " & StreetType & CHAR(10) & CityName & " " & StateCode & " " & ZipCode & CHAR(10) & CountryName
And the exclusive use of CONCATENATE
=CONCATENATE(StreetNumber," ",StreetName," ",StreetType,CHAR(10),CityName," ",StateCode," ",ZipCode,CHAR(10),CountryName)
Then again, I'd argue that a UDF like BuildAddress
would be a better solution (and would be better placed to handle the subtleties of address formatting in internationalization domains  although I haven't implemented that)...
Public Function BuildAddress(ByVal StreetNumber As String, ByVal StreetName As String, _
ByVal StreetType As String, ByVal CityName As String, ByVal StateCode As String, _
ByVal ZipCode As String, ByVal CountryName As String) As String
BuildAddress = StreetNumber & " " & StreetName & " " & StreetType & vbCrLf & _
CityName & " " & StateCode & " " & ZipCode & vbCrLf & _
CountryName
End Function
But perhaps another example, that includes the use of &
within the string literal, better demonstrates the difficulty of reading a formula that forces itself to exclusively use operators:
=A1&"A2&A3&A4"&A5
Might be better written as:
=CONCATENATE(A1,"A2&A3&A4",A5)
But the performance is what matters, and depending upon the number of arguments being concatenated, and the length of each argument, the CONCATENATE
function would appear to outperform the concatenation operator by a factor of between 4 and 6. Admittedly, this example is extreme, with 255 arguments being concatenated, 10,000 times. I do not recommend using an argument string length greater than 32, or you may run out of memory/crash Excel.
Here's a crude timing mechanism:
Option Explicit
Const ConcatenationOperatorFormula As String = _
"=$A$1&$A$2&$A$3&$A$4&$A$5&$A$6&$A$7&$A$8&$A$9&$A$10&$A$11&$A$12&$A$13&$A$14&$A$15&$A$16&$A$17&$A$18&$A$19&$A$20&$A$21&$A$22&$A$23&$A$24&$A$25&$A$26&$A$27&$A$28&$A$29&$A$30&$A$31&$A$32&$A$33&$A$34&$A$35&$A$36&$A$37&$A$38&$A$39&$A$40&$A$41&$A$42&$A$43&$A$44&$A$45&$A$46&$A$47&$A$48&$A$49&$A$50&$A$51&$A$52&$A$53&$A$54&$A$55&$A$56&$A$57&$A$58&$A$59&$A$60&$A$61&$A$62&$A$63&$A$64&$A$65&$A$66&$A$67&$A$68&$A$69&$A$70&$A$71&$A$72&$A$73&$A$74&$A$75&$A$76&$A$77&$A$78&$A$79&$A$80&$A$81&$A$82&$A$83&$A$84&$A$85&$A$86&$A$87&$A$88&$A$89&$A$90&$A$91&$A$92&$A$93&$A$94&$A$95&$A$96&$A$97&$A$98&$A$99&$A$100&" & _
"$A$101&$A$102&$A$103&$A$104&$A$105&$A$106&$A$107&$A$108&$A$109&$A$110&$A$111&$A$112&$A$113&$A$114&$A$115&$A$116&$A$117&$A$118&$A$119&$A$120&$A$121&$A$122&$A$123&$A$124&$A$125&$A$126&$A$127&$A$128&$A$129&$A$130&$A$131&$A$132&$A$133&$A$134&$A$135&$A$136&$A$137&$A$138&$A$139&$A$140&$A$141&$A$142&$A$143&$A$144&$A$145&$A$146&$A$147&$A$148&$A$149&$A$150&$A$151&$A$152&$A$153&$A$154&$A$155&$A$156&$A$157&$A$158&$A$159&$A$160&$A$161&$A$162&$A$163&$A$164&$A$165&$A$166&$A$167&$A$168&$A$169&$A$170&$A$171&$A$172&$A$173&$A$174&$A$175&$A$176&$A$177&$A$178&$A$179&$A$180&$A$181&$A$182&$A$183&$A$184&$A$185&$A$186&$A$187&$A$188&$A$189&$A$190&$A$191&$A$192&$A$193&$A$194&$A$195&$A$196&$A$197&$A$198&$A$199&$A$200&" & _
"$A$201&$A$202&$A$203&$A$204&$A$205&$A$206&$A$207&$A$208&$A$209&$A$210&$A$211&$A$212&$A$213&$A$214&$A$215&$A$216&$A$217&$A$218&$A$219&$A$220&$A$221&$A$222&$A$223&$A$224&$A$225&$A$226&$A$227&$A$228&$A$229&$A$230&$A$231&$A$232&$A$233&$A$234&$A$235&$A$236&$A$237&$A$238&$A$239&$A$240&$A$241&$A$242&$A$243&$A$244&$A$245&$A$246&$A$247&$A$248&$A$249&$A$250&$A$251&$A$252&$A$253&$A$254&$A$255"
Const ConcatenateFunctionFormula As String = _
"=CONCATENATE($A$1,$A$2,$A$3,$A$4,$A$5,$A$6,$A$7,$A$8,$A$9,$A$10,$A$11,$A$12,$A$13,$A$14,$A$15,$A$16,$A$17,$A$18,$A$19,$A$20,$A$21,$A$22,$A$23,$A$24,$A$25,$A$26,$A$27,$A$28,$A$29,$A$30,$A$31,$A$32,$A$33,$A$34,$A$35,$A$36,$A$37,$A$38,$A$39,$A$40,$A$41,$A$42,$A$43,$A$44,$A$45,$A$46,$A$47,$A$48,$A$49,$A$50,$A$51,$A$52,$A$53,$A$54,$A$55,$A$56,$A$57,$A$58,$A$59,$A$60,$A$61,$A$62,$A$63,$A$64,$A$65,$A$66,$A$67,$A$68,$A$69,$A$70,$A$71,$A$72,$A$73,$A$74,$A$75,$A$76,$A$77,$A$78,$A$79,$A$80,$A$81,$A$82,$A$83,$A$84,$A$85,$A$86,$A$87,$A$88,$A$89,$A$90,$A$91,$A$92,$A$93,$A$94,$A$95,$A$96,$A$97,$A$98,$A$99,$A$100," & _
"$A$101,$A$102,$A$103,$A$104,$A$105,$A$106,$A$107,$A$108,$A$109,$A$110,$A$111,$A$112,$A$113,$A$114,$A$115,$A$116,$A$117,$A$118,$A$119,$A$120,$A$121,$A$122,$A$123,$A$124,$A$125,$A$126,$A$127,$A$128,$A$129,$A$130,$A$131,$A$132,$A$133,$A$134,$A$135,$A$136,$A$137,$A$138,$A$139,$A$140,$A$141,$A$142,$A$143,$A$144,$A$145,$A$146,$A$147,$A$148,$A$149,$A$150,$A$151,$A$152,$A$153,$A$154,$A$155,$A$156,$A$157,$A$158,$A$159,$A$160,$A$161,$A$162,$A$163,$A$164,$A$165,$A$166,$A$167,$A$168,$A$169,$A$170,$A$171,$A$172,$A$173,$A$174,$A$175,$A$176,$A$177,$A$178,$A$179,$A$180,$A$181,$A$182,$A$183,$A$184,$A$185,$A$186,$A$187,$A$188,$A$189,$A$190,$A$191,$A$192,$A$193,$A$194,$A$195,$A$196,$A$197,$A$198,$A$199,$A$200," & _
"$A$201,$A$202,$A$203,$A$204,$A$205,$A$206,$A$207,$A$208,$A$209,$A$210,$A$211,$A$212,$A$213,$A$214,$A$215,$A$216,$A$217,$A$218,$A$219,$A$220,$A$221,$A$222,$A$223,$A$224,$A$225,$A$226,$A$227,$A$228,$A$229,$A$230,$A$231,$A$232,$A$233,$A$234,$A$235,$A$236,$A$237,$A$238,$A$239,$A$240,$A$241,$A$242,$A$243,$A$244,$A$245,$A$246,$A$247,$A$248,$A$249,$A$250,$A$251,$A$252,$A$253,$A$254,$A$255)"
Const ARGUMENT_STRING_LENGTH As Long = 1
Sub test2()
Dim start As Single
'Disable app events to exclude UI/calculation artefacts
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Dim inputs As Range
Set inputs = Range("A1:A255")
Dim operatorRange As Range
Set operatorRange = Range("B1:B10000")
Dim functionRange As Range
Set functionRange = Range("C1:C10000")
'Reset the range values/formulas
inputs.Value2 = ""
operatorRange.Formula = ConcatenationOperatorFormula
functionRange.Formula = ConcatenateFunctionFormula
'Change the inputs to invalidate the calculation results
inputs.Value2 = String(ARGUMENT_STRING_LENGTH, "B")
'Time the calculation of operator formulas
start = Timer
operatorRange.Calculate
Debug.Print "Operator Calculation", ARGUMENT_STRING_LENGTH, FormatNumber(Timer  start, 8)
'Time the calculation of function formulas
start = Timer
functionRange.Calculate
Debug.Print "Function Calculation", ARGUMENT_STRING_LENGTH, FormatNumber(Timer  start, 8)
'Reset the range values to empty
inputs.Value2 = ""
operatorRange.Value2 = vbNullString
functionRange.Value2 = vbNullString
'Restore App defaults
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub
And the results, according to the length of the argument strings:
Method String Length Seconds
  
Function Calculation 1 0.06640625
Operator Calculation 1 0.21484380
Function Calculation 10 0.09765625
Operator Calculation 10 0.47265630
Function Calculation 32 0.17578130
Operator Calculation 32 1.17968800
But then, we haven't even discussed the elephant in the room. You're building a SQL command using concatenation. Don't do that. You're executing a stored procedure that accepts parameters. Unless you've sanitized your inputs (and I'm guessing you haven't), building a SQL string using concatenation is asking for a SQLinjection attack. You might as well expose a UDF called JohnnyDropTables
...

You are making a healthy amount of assumptions on that last part.... – Taylor Ackley Aug 3 '17 at 15:18
It is a semantic domain difference. Concatenate is the name of a spreadsheet function. The Ampersand is a concatenation operator borrowed from Visual Basic. Folks who never open use VBA would find a function much easier to use than VBA Syntax. It's the same reason why there is a hotkey, an icon, and a menu option to save, which is making software easier to use.
I use both.
For long lists which I might need to review visually, a comma takes up less eyespace than an ampersand.
It's easier to read a list of cells separated by commas than separated by ampersands, especially since an ampersand looks (after a 15 hour day) too much like a $.
This provides a meaningful role for CONCATENATE.
But  agree  there's no speed benefit, or any functional difference.
One particular use case is that =CONCATENATE(A1:A10)
is a lot shorter than =A1&A2&A3&A4&A4&A6&A7&A8&A9&A10
. It is also much more obviously correct (in fact the example &
version has a deliberate mistake).
I tried this first, but I was using the office Excel, which is in German. TEXTKETTE
behaves as I describe, but the documentation shows it is a newish function, and replaces VERKETTEN
(which will be the German equivalent of CONCATENATE
).

3@Vylix: No, I mean that the
CONCATENATE
form is more obviously correct. If you want to concatenate all the elements of a range, giving the range is much less errorprone, than giving each element one by one. – Martin Bonner Jul 6 '17 at 14:58 
6

6@MartinBonner
=CONCATENATE(A1:A10)
does not work in Excel. If you have such a function, it's a custom UDF, not native Excel. But it can't be a UDF with the same name as the native function. – teylyn Jul 6 '17 at 19:58 
3@MartinBonner Your argument might hold for a function like
SUM
and operators like+
, but it does not apply to Excel'sCONCATENATE
function.=CONCATENATE(A1:A2)
is never the same result as=A1&A2
– ThunderFrame Jul 6 '17 at 23:49 
=CONCATENATE(A1:A10)
gives the result ofA1
, so it's obviously not only shorter but different – phuclv Jul 7 '17 at 1:59
I didn't see the true answers in here, but do have some insight. (for anyone that may in future look up this answer)
"Concatenate" is an older legacy function, which does do the same things as the "&", "&" was added later on for consistency in programming languages. However, "Concatenate" has been replaced by "Concat" to support ranges, so you can combine A1:A10 without needing any custom scripting. The "&" still does not allow a range and only takes the first cell in the same way "Concatenate" functions. So if combining a range, the "Concat" gives that extra function change over the previous 2 styles that are just coded differently. Making "Concatenate" and "&" essentially a preference when using nonrange required combined strings on how you like to type it out.
https://support.office.com/enie/article/concatfunction9b1a9a3f94ff41af9736694cbd6b4ca2

"CONCATENATE may not be available in future versions of Excel." Haha, right. They still support
=
alternatives  entering functions like@CONCATENATE(A1,A2)
and calculations like+A5+A6
orA5+A6
. – Džuris Jul 6 '17 at 13:35 
2

2
CONCATENATE()
and&
are internally implemented differently in Excel? – GMan Jul 30 '17 at 7:17