39

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

  • 2
    In the case of a long sequence of string concatenations, CONCATENATE() has the potential to be linear instead of quadratic in the length of the final string. see joelonsoftware.com/2001/12/11/back-to-basics – Pieter Geerkens Jul 8 '17 at 7:20
  • 1
    Anecdotal, but I often use CONCATENATE when joining over three strings, for the sole reason that I am more used to separating arguments with commas than with ampersands... though of course that is not really a valid reason. – Alexandre d'Entraigues Jul 12 '17 at 17:32
  • 1
    @PieterGeerkens: I have no idea what your point is. OK, a long sequence of independently performed string concatenations has the potential to be either linear or quadratic in the length of the final string, depending on how you store the strings in memory. Do you have any evidence (or any reason to believe) that CONCATENATE() and & are internally implemented differently in Excel? – G-Man Jul 30 '17 at 7:17
54

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.

enter image description here

  • 5
    At 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
    Didn't you mean =Sum(A1,A2) (as an alternative to =A1+B1)? – xehpuk Jul 6 '17 at 9:25
  • 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(A1-A2) where they could us =A1-A2. 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
  • 4
    -1. 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 uncalled-for; there are reasons why they do that and it's not their stupidity. – AnoE Jul 7 '17 at 14:09
23

It's probably because they're using the Insert Function button.

enter image description here

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
  • 3
    To concat strings I need to remember dot . for perl, pipe-pipe || 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 than concatenate, 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 VERKETTENin 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
18

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 for CONCATENATE 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
16

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

  • You are making a healthy amount of assumptions on that last part.... – Taylor Ackley Aug 3 '17 at 15:18
4

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.

1

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.

0

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 error-prone, than giving each element one by one. – Martin Bonner Jul 6 '17 at 14:58
  • 6
    The specific example of the & form has a deliberate mistake. – Martin Bonner Jul 6 '17 at 15:01
  • 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's CONCATENATE 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 of A1, so it's obviously not only shorter but different – phuclv Jul 7 '17 at 1:59
-6

RTFM Microsoft says use the &.

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

enter image description here

  • "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 or -A5+A6. – Džuris Jul 6 '17 at 13:35
  • 2
    "Read The F'n Manual" isn't exactly what be nice is about. – Mathieu Guindon Jul 6 '17 at 20:51
  • 2
    @Mat'sMug The F can also stand for Fine :) – DavidPostill Jul 6 '17 at 21:35

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.