Why use CONCATENATE vs & in Excel?

Question

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 &?

Answer 1

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.

Answer 2

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.

Answer 3

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 &.

Answer 4

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 SQL-injection attack. You might as well expose a UDF called JohnnyDropTables...

Answer 5

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 hot-key, an icon, and a menu option to save, which is making software easier to use.

Answer 6

I use both.

For long lists which I might need to review visually, a comma takes up less eye-space 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.

Answer 7

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).

Answer 8

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 non-range required combined strings on how you like to type it out.

https://support.office.com/en-ie/article/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2

Answer 9

RTFM Microsoft says use the &.

BTW, you will get different answers when using the SUM operator.