How to concatenate strings in Excel

Question

This question comes from a comment to Range.Formula= in VBA throws a strange error.

I wrote that program by trial-and-error method so I naturally tried + to concatenate strings. But is & rather than + more correct method for concatenating strings?

Answer 1

& is always evaluated in a string context, while + may not concatenate if one of the operands is no string:

"1" + "2" => "12"
"1" + 2   => 3
1 + "2"   => 3
"a" + 2   => type mismatch

This is simply a subtle source of potential bugs and therefore should be avoided. & always means "string concatenation", even if its arguments are non-strings:

"1" & "2" => "12"
"1" &  2  => "12"
 1  & "2" => "12"
 1  &  2  => "12"
"a" &  2  => "a2"

Answer 2

There is the concatenate function. For example

=CONCATENATE(E2,"-",F2)

But the & operator always concatenates strings. + often will work, but if there is a number in one of the cells, it won't work as expected.

Answer 3

excel has a Concatenate() function...