Excel VBA Concatenate Strings: Methods and Examples

VBA & macros · Updated November 21, 2026

In Excel VBA, concatenate strings with the ampersand operator (&), which joins text values into one string. For example, firstName & " " & lastName combines two names with a space between them; use Join when you need to combine the items in an array with a repeated separator.

What Concatenation Means in VBA

Concatenation is the process of joining pieces of text end to end. A piece may be a string literal, a variable, or a value converted from a cell. VBA evaluates the pieces from left to right and returns a combined string. You can assign that result to a string variable, write it to a worksheet cell, display it in a message box, or pass it to another procedure.

For example, if firstName contains “Maya” and lastName contains “Chen,” the expression firstName & " " & lastName produces “Maya Chen.” The spaces and punctuation are not added automatically: include them as quoted text wherever you want them to appear.

In the Visual Basic Editor, a procedure can be added in a standard module. On Windows, press Alt+F11 to open the editor; on Mac, the usual shortcut is Option+F11, although some keyboards require Fn as well. You can also open the editor from the Developer tab. Add the Developer tab through Excel Options on Windows or Excel > Settings on Mac if it is not visible.

Use the Ampersand Operator

The ampersand is the clearest general-purpose operator for string concatenation in VBA. It works well for two pieces of text and for longer expressions containing several pieces. Assign the result to a variable or directly to a destination cell, as in this example. It assumes first and last names are in cells A2 and B2 on Sheet1 and writes the combined name to C2:

Option Explicit

Sub CombineName()
    Dim firstName As String
    Dim lastName As String

    ' Read the two source cells.
    firstName = CStr(Worksheets("Sheet1").Range("A2").Value)
    lastName = CStr(Worksheets("Sheet1").Range("B2").Value)

    ' Write the combined name to a separate cell.
    Worksheets("Sheet1").Range("C2").Value = firstName & " " & lastName
End Sub

The result is written as a value, not as a worksheet formula. If A2 contains Maya and B2 contains Chen, C2 receives Maya Chen. The use of CStr converts ordinary values to strings; it does not guarantee that every possible cell value can be converted safely. For example, an Excel error value needs to be handled before conversion.

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, and a macro can change or replace data without adding a reversible action to Excel’s undo history. This example writes to C2, so check that the destination is suitable before running it.

Choose Between & and the Plus Sign

VBA also allows the plus sign to join strings in some situations, but it is usually a poor choice when the intention is text concatenation. The plus sign can mean numeric addition as well as string joining, so the result can depend on the data types of the expressions. Mixing numbers and text can produce confusing behavior or a type mismatch.

The ampersand communicates the intended operation: join these values as text. For instance, "Order " & orderNumber makes a text label. By contrast, price + tax is naturally read as arithmetic. Prefer & for building text, even when the current example happens to work with +. It makes code easier to understand and reduces ambiguity when a variable’s type or contents change.

There is an important data-handling detail: a cell can contain a number, a date, a blank, or an error even if it looks like text on the worksheet. VBA’s conversion rules and the cell’s stored value affect the output. If a number needs a specific display format, such as two decimal places or leading zeroes, format it deliberately rather than assuming that concatenation will preserve the worksheet’s visual appearance.

Add Separators, Labels, and Line Breaks

VBA does not insert separators between joined strings. Put separators in quoted literals where they belong: a space for a full name, a comma and space for a list, or a colon after a label. For example, "City: " & city produces a labeled value, while firstName & lastName joins the names with no gap.

For a line break inside a cell, join the parts with vbLf. This example assumes the two text values are already held in the variables addressLine1 and addressLine2 and writes them to B2 on Sheet1:

Option Explicit

Sub WriteAddress()
    Dim addressLine1 As String
    Dim addressLine2 As String
    Dim outputCell As Range

    addressLine1 = "25 Oak Street"
    addressLine2 = "Springfield"
    Set outputCell = Worksheets("Sheet1").Range("B2")

    ' Excel cells use a line-feed character for an in-cell line break.
    outputCell.Value = addressLine1 & vbLf & addressLine2
    outputCell.WrapText = True
End Sub

With Wrap Text enabled, Excel can display the two lines within the cell; row height may need adjustment to show both. This is a line break in the cell’s content, not a new row in the worksheet. For a message box or a text file, the appropriate newline convention may differ, so choose the separator for the destination.

Join Multiple Items with Join

When the pieces are already in an array, VBA’s Join function is a convenient alternative to repeatedly writing the ampersand operator. It takes an array and an optional delimiter, then returns one string. This example uses three fixed items and places a comma and space between them:

Option Explicit

Sub JoinItems()
    Dim items As Variant
    Dim result As String

    items = Array("Red", "Green", "Blue")
    result = Join(items, ", ")

    Worksheets("Sheet1").Range("A1").Value = result
End Sub

The value written to A1 is Red, Green, Blue. The delimiter is repeated between array elements, not added at the beginning or end. If the delimiter argument is omitted, Join uses a space. This function is especially useful when an array has many entries or when the separator may change, because the joining rule is kept in one place.

Join expects an array. It is not a direct replacement for concatenating an arbitrary worksheet range in every situation: range values need to be read and, where necessary, converted or checked first. For a small fixed set of values, & is often simpler. For a list assembled in an array, Join is generally more readable.

Concatenate Values from a Worksheet Range

For a vertical range, a loop gives you control over blanks and separators. This example assumes the values to combine are in A2:A5 on Sheet1. It skips empty cells and writes the result to B2; ordinary text or numbers are expected in the source cells:

Option Explicit

Sub CombineRangeValues()
    Dim sourceCell As Range
    Dim result As String
    Dim valueText As String

    For Each sourceCell In Worksheets("Sheet1").Range("A2:A5")
        ' Convert the current cell value to text.
        valueText = CStr(sourceCell.Value)

        If Len(valueText) > 0 Then
            If Len(result) > 0 Then result = result & ", "
            result = result & valueText
        End If
    Next sourceCell

    Worksheets("Sheet1").Range("B2").Value = result
End Sub

If the four cells contain North, a blank, East, and West, the result is North, East, West. The conditional separator avoids a leading comma and also means blank cells do not create doubled separators. If empty values are meaningful positions in the output, use a different rule instead of skipping them.

For production code, check for cell errors before calling CStr; otherwise, an error value can cause a runtime error. Also decide whether to use the underlying cell value or its displayed text. Value or Value2 reads stored values, while Text reflects the cell’s displayed appearance and can be affected by column width and number formatting. For dates and formatted numbers, explicitly choose the representation you want in the final string.

Common Problems and How to Avoid Them

Most concatenation issues come from assumptions about separators, cell values, or where the result is written. Check these points when the output is unexpected:

  • Missing spaces: VBA does not add them for you. Include a literal such as " " between name components.
  • Unexpected number or date display: Concatenation uses values, not necessarily the visible formatting. Apply a deliberate conversion or formatting approach if the exact presentation matters.
  • Error values: Check cells with IsError before converting them to strings, and decide whether to skip them or substitute a message.
  • Blank cells creating extra punctuation: Skip blanks or track whether an item has already been appended before adding the next delimiter.
  • Result overwrites source data: Write to a separate destination range and verify it before running the macro.
  • Macro does not run: Save the workbook in a macro-enabled format such as .xlsm, and follow your organization’s macro security policy. Do not enable macros from an untrusted file.

Another misconception is that changing a cell’s number format changes the string VBA has already built. Formatting a cell affects how Excel displays values in cells, but the concatenated result is text. Decide on the intended text representation before joining the parts.

When to Use VBA and When to Use a Worksheet Formula

Use VBA when the concatenation is part of a repeatable procedure, when it depends on conditions or a loop, or when the result needs to be written as part of a larger automation task. A macro can combine values, apply rules for blanks, and place the output where the workflow needs it.

For a result that should update automatically when source cells change, a worksheet formula may be more suitable. In Microsoft 365, a formula can join text directly in the grid, while VBA generally requires running a macro again to refresh a value it previously wrote. Excel’s worksheet functions and VBA’s language operators are separate tools: the VBA ampersand operator is used in code, whereas a worksheet formula is entered in a cell and begins with an equals sign.

There is no need to use a macro merely because the text comes from several cells. If the task is a simple, visible calculation, a formula is often easier to audit and updates without a macro run. If you need workbook automation or custom logic over many rows, VBA may be the better fit.

Frequently Asked Questions

What symbol concatenates strings in VBA?
Use the ampersand operator, &. For example, "Hello, " & userName joins the greeting and the value of userName.

Can I use + to concatenate strings in VBA?
It may join strings in some cases, but + can also perform addition. Use & when you mean text concatenation so the intent is clear and numeric values are less likely to create ambiguity.

How do I concatenate cells with a comma between them?
For a few known cells, join their values with & ", " &. For a variable-length list, loop through the range or put the values in an array and use Join with ", " as the delimiter.

How do I add a line break between concatenated strings?
Use vbLf between the strings for an in-cell line break, and enable Wrap Text on the destination cell so the lines can be shown.

Why does my concatenated date or number look different?
VBA may convert the stored value to text differently from the way Excel displays the formatted cell. Choose and apply the text representation you want before joining, and handle dates, decimal places, or leading zeroes explicitly.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.