VBA Concatenate in Excel — & vs + and the Null Trap That Breaks Your Strings

TL;DR — VBA gives you two operators that look like they both join text: & and +. Only one is safe. & always treats both sides as text — it turns numbers into their digits and treats Null as an empty string. + does arithmetic when it sees numbers and goes contagious when it sees Null (one Null makes the whole result Null). Rule with no exceptions: use & to build strings, keep + for math. And when you're gluing thousands of pieces in a loop, stop using s = s & x — that's O(n²). Use Join on an array instead.

Concatenation is the most common thing you do with text in VBA: build a file name, a message, a SQL string, a report title. It looks trivial — until a number or a blank cell turns your "Total: " + value into either a wrong number or a silent Null, and the bug ships because nothing errored.

What you'll learn

• Why & is the only concatenation operator you should trust (and what + really does)
• The Null trap that makes + zero out an entire expression
• Why s = s & item inside a loop is secretly O(n²) — and the Join fix
• The whitespace habit that prevents the &-as-type-character bug

The mental model: & is glue, + is a counterfeit glue that betrays you

Picture & as a tube of glue labelled "text only." Whatever you hand it — a string, a number, a date, even a blank — it coerces to text and sticks it on the end. + is an identical-looking tube, but it's labelled "math first." When both sides happen to be strings, it glues. The moment one side is a number or a Null, it stops being glue and does arithmetic — and you don't find out until the output is wrong.

Sub TwoOperators()
    ' & always concatenates as text
    Debug.Print "Item " & 5          ' -> "Item 5"   (number coerced to text)
    Debug.Print "5" & "5"            ' -> "55"        (glued)

    ' + does math the instant a number is involved
    Debug.Print "5" + "5"            ' -> "55"  ... but only because both are TEXT
    Debug.Print 5 + 5               ' -> 10    (arithmetic)
    Debug.Print "Item " + 5         ' -> run-time error 13: Type mismatch
End Sub

That last line is the tell: "Item " + 5 blows up because + tries to add a number to a string. & never has this problem — "Item " & 5 just works.

The rule that prevents the worst concatenation bug: the Null trap

Here is the failure mode that actually corrupts data. When you read a value from a database recordset, an empty variant, or a cell that came back as Null, the two operators behave in opposite ways:

Sub TheNullTrap()
    Dim middleName As Variant
    middleName = Null                       ' e.g. an empty field from ADO

    ' & treats Null as an empty string — safe
    Debug.Print "Anna " & middleName & " Smith"   ' -> "Anna  Smith"

    ' + is contagious: ONE Null poisons the whole expression
    Debug.Print "Anna " + middleName + " Smith"   ' -> Null  (everything gone)
End Sub

With +, a single Null anywhere in the chain makes the entire result Null. If you then write that to a cell, you get a blank where a name should be — and no error to tell you why. This is the number-one reason "my concatenation randomly produces empty cells" bug reports exist. The fix is not to check every value for Null. The fix is to never use + to build a string. Reserve + for cases where you genuinely want Null propagation as a signal — which, in practice, is almost never.

The whitespace habit: & is also a type-declaration character

A subtle one that bites people who write compact code. In VBA, & appended to a variable name is the old type-declaration suffix for Long (like $ for String). So if you jam & against a name with no space, the parser can read it as a type character instead of the concatenation operator:

    total = count&"x"      ' ambiguous — VBE may misread count& as a Long
    total = count & "x"    ' unambiguous — always concatenation

Always put spaces around &. It's not cosmetic; it removes a real parsing ambiguity, and it makes long concatenations readable. Use vbNewLine, vbCrLf, and vbTab as constants between pieces rather than hard-coding Chr(10):

    msg = "Line one" & vbNewLine & "Line two" & vbTab & "(indented)"

Why your concatenation is slow: the O(n²) loop

This is the performance judgment that separates a macro that finishes in a blink from one that hangs for 30 seconds. VBA strings are immutable — every s = s & item allocates a brand-new string and copies everything that came before. Do that 10,000 times in a loop and you've copied the growing string 10,000 times: classic O(n²).

' SLOW — rebuilds the whole string every iteration (O(n²))
Dim s As String, i As Long
For i = 1 To 10000
    s = s & data(i) & ","
Next i

' FAST — collect into an array, glue once with Join (O(n))
Dim parts() As String
ReDim parts(1 To 10000)
For i = 1 To 10000
    parts(i) = data(i)
Next i
s = Join(parts, ",")          ' one allocation

Join is the inverse of Split: it takes an array and a delimiter and returns one string in a single allocation. For anything past a few hundred iterations, building an array and calling Join once is dramatically faster — and reads better — than accumulating with &.

The judgment call: for a handful of pieces, & is perfect and Join would be overkill. For a loop that builds a big string, reach for Join every time.

Concatenating a range of cells

A very common ask: glue a column of cells into one delimited string. Don't loop cell-by-cell if you can avoid it — Excel's own TEXTJOIN (available via WorksheetFunction) does it in one call and skips blanks for you:

Sub JoinAColumn()
    Dim result As String
    ' TEXTJOIN(delimiter, ignore_empty, range)
    result = WorksheetFunction.TextJoin(", ", True, Range("A1:A50"))
    Debug.Print result
End Sub

If you do need a VBA loop (older Excel without TEXTJOIN), read the range into a variant array first, build a String array, and Join it — never touch the worksheet 50 times inside the loop.

How ExcelMaster helps

A lot of concatenation VBA exists to assemble one thing: a clean export — a CSV line, a formatted label, a key for matching. ExcelMaster builds those from a plain-English description: "make a key column from customer ID plus order date, comma-separated" — and it handles the blanks, the types, and the delimiters without you reasoning about & vs + or Null propagation.

You'll still write VBA when concatenation lives inside an always-on macro. But for one-off "stitch these columns into an export" jobs, describing the output is faster than getting the operators right by hand.

Frequently asked questions

What is the difference between & and + in VBA?

& always concatenates as text — it coerces numbers to their digits and treats Null as an empty string. + does arithmetic when a number is involved and propagates Null (one Null makes the whole expression Null). Use & for building strings and + only for math.

How do I add a new line in a concatenated string?

Insert the vbNewLine constant (or vbCrLf) between pieces: "Line 1" & vbNewLine & "Line 2". For a tab, use vbTab. These read better and are more portable than hard-coding Chr(13) and Chr(10).

Why is my VBA string concatenation so slow?

You're almost certainly accumulating with s = s & x inside a large loop, which is O(n²) because VBA rebuilds the whole string each time. Collect the pieces into a String array and call Join(parts, delimiter) once.

How do I concatenate a range of cells in VBA?

Use WorksheetFunction.TextJoin(delimiter, ignore_empty, range) for a one-line solution that also skips blanks. Only fall back to a loop on older Excel versions that lack TEXTJOIN, and even then read the range into an array and Join it rather than looping over cells.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-06-14.

Related guides: VBA UCase & LCase · VBA Str · VBA Split · VBA CStr · VBA For Loop