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 &?
10 Answers
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.
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.
8It 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 &.
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) & CountryNameThan the exclusive use of &:
=StreetNumber & " " & StreetName & " " & StreetType & CHAR(10) & CityName & " " & StateCode & " " & ZipCode & CHAR(10) & CountryNameAnd 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 FunctionBut 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"&A5Might 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 SubAnd 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.17968800But 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...
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.
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.
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.
Wanted to add that CONCATENATE/CONCAT can accept an array range of cells. In the snip, a unique item number is being created simply by CONCAT(cellRange). It's faster than typing =A1&B1&C1....
Use range arguments with CONCAT
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).
RTFM Microsoft says use the &.
BTW, you will get different answers when using the SUM operator.
